if - else statement in sql

begg

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,

Corporal Agarn

Look at @@RowCount[^] Remember that it changes with the next executed statement.

Michael Potter

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'

vvashishta

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

rimazuc

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

rimazuc

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

hearthofmine

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 :)