|
Hi,
I have a list to populate. I have a query to populate it in mssql 2008. It involves three tables. If the query returns the value then I populate it and it is done. But , if the query doesnot return anything then I want to run another select statement because I do not want to leave the list view empty. I have to populate at least the names of the client. It could be something simple but I am just beginning to use mssql so I am stuck.
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
if does not return anything,
I want to run another sql to atleast get the name of the client even if there is no related detail with that client.
second sql would be
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= '100'
[P.S. the bID field of Table A could actually either be bID as is or C.clientID inserted into bID field of Table C.]
I am confident about the queries but its just about extracting the values from the result.
Thanks,
|
|
|
|
|
Look at @@RowCount[^]
Remember that it changes with the next executed statement.
|
|
|
|
|
I don't understand the LEFT JOIN between A.bID and C.clientID. You infer that the second query will always get the answer you are looking for so, an INNER JOIN is appropriate. With further examiniation, only B.Detail may be NULL if you have a valid aID. It is usually easier to let the client handle the NULL column than two types of data structures.
The query below will always have data in Client. Detail may or may not be NULL. This is assuming that aID is valid.
SELECT
C.name AS Client,
B.Detail AS Detail
FROM
tableC C
INNER JOIN
tableA A
ON (C.clientID = A.bID)
LEFT JOIN
tableB B
ON (C.clientID = B.clientID)
WHERE
aID = '100'
|
|
|
|
|
You can use the Case Statement for this:
Declare a variable @Client Varchar(100) = NULL, now put
Select C.name as Client , @Client = C.name,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= '100';
Now check in Case like this::
CASE WHEN @Client IS NULL THEN
... Put Your Another Select Query Here ...
END
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Hi There
You can do a rowcount and if a count on the no of rows is zero, then Select .....
For example
IF EXISTS (SELECT COUNT(*) FROM tablename)
BEGIN
SELECT ..... FROM TABLENAME
END
-- if no data then do... that is if row count is zero
ELSE
SELECT ..... FROM TABLENAME
|
|
|
|
|
Another method is to use a variable to hold the number of records and based on the number of records, decide which sql statement to execute.
For example
--PUR ROW COUNT IN A VARIABLE
DECLARE @rowcount INT
SELECT @rowcount = COUNT(*) FROM tablename
IF @rowcount >= 27 --SPECIFY the number expected
BEGIN
PRINT 'TEST-1' --OR SELECT STATEMENT
END
ELSE
BEGIN
PRINT 'TEST-2' -- OR SELECT STATEMENT
END
hope this helps.
Cheers
|
|
|
|
|
TRY THIS
Declare @rowcount int
select @rowcount = (Select count(*) from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100)
if @rowcount > 1
begin
Select C.name as Client ,
B.Detail as Detail from tableA A
left join tableB B on B.bID = A.bID
Left join tableC C on C.clientID = B.clientID
where aID= 100
else
Select C.name as Client from tableA A
Left join tableC C on C.clientID = A.bID
where aID= 100
end
Hope this will help 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
