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.
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.
C.name AS Client,
B.Detail AS Detail
ON (C.clientID = A.bID)
ON (C.clientID = B.clientID)
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
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.
--PUR ROW COUNT IN A VARIABLE
DECLARE @rowcount INT
SELECT @rowcount = COUNT(*) FROM tablename
IF @rowcount >= 27 --SPECIFY the number expected
PRINT 'TEST-1' --OR SELECT STATEMENT
PRINT 'TEST-2' -- OR SELECT STATEMENT