How to place on condition on a select statement to use certain tables

is this what you are looking for

sorry a copy error... here is the code

SELECT Table1.vst_int_id, Table1.diag_ty, Table2.diag_ds,Table3.HtFcode, Table4.LVADcode,
		CASE WHEN Table1.diag_ty = 'P' THEN Table3.HtFcode
			 WHEN Table1.diag_ty = 'S' THEN Table4.LVADcode
		END AS yourColumn
FROM Table1 
		INNER JOIN Table2 ON Table1.diag_int_id = Table2. Diag_int_id 
		INNER JOIN Table3.HtFcode ON Table2.diag_code = Table3.HtFcode 
		INNER JOIN Table4.LVADcode ON Table2.diag_code = Table4.LVADcode

Select allOpen in new window

OK...

First up the JOINS are for joining tables (not specific columns) and the JOIN is basically saying "show me the rows that exist as per the join" in your case with INNER joins, the data must exist in all those table. If some of them might not have an entry, then you can use LEFT OUTER JOINS which means it doesnt matter if the table you are joining to actually has a row in there or not.

By the looks of things table2 must be an inner join to table1. But table3 and table 4 might not have rows - so - might also work with LEFT OUTER JOIN if they are optional. Right now all four tables must have rows according to their link (joins) before a row can be selected

For conditional selects you can use the CASE statement (which is kinda like an IF statement). One way to read your requirement is a conditional which chooses whic vst_int_id's to display - either from table3 or table4 depending on diag_ty. To me that sounds like a CASE statement.

SELECT Table1.vst_int_id, Table1.diag_ty, Table2.diag_ds,
             Table3.HtFcode, Table4.LVADcode,
             CASE WHEN Table1.diag_ty = 'P' then Table3.vst_int_id
                      WHEN Table1.diag_ty = 'S' then Table4.vst_int_id
             ELSE NULL
             END  as My_New_Column

FROM Table1
INNER JOIN Table2 ON Table1.diag_int_id = Table2. Diag_int_id
INNER JOIN Table3 ON Table2.diag_code = Table3.HtFcode
INNER JOIN Table4 ON Table2.diag_code = Table4.LVADcode  

There is also another way of reading your question and that is to join to either table3 or table4 depending on diag_ty. Because diag_ty can only be one value at a time, we can no longer have INNER JOINS throughout - some rows will resolve to Table3 and other rows will resolve to Table4. So, the SQL might now look like :

SELECT Table1.vst_int_id, Table1.diag_ty, Table2.diag_ds,
             Table3.HtFcode, Table4.LVADcode

FROM Table1
INNER JOIN Table2 ON Table1.diag_int_id = Table2. Diag_int_id
LEFT OUTER JOIN Table3 ON Table2.diag_code = Table3.HtFcode and Table1.diag_ty = 'P'
LEFT OUTER JOIN Table4 ON Table2.diag_code = Table4.LVADcode  and Table1.diag_ty = 'S'  

Here either Table3.htfcode will show a value (if the jon can be resolved for diag_ty = 'P') or NULL.
Similarly, Table4.LVADcode will show a value or NULL.
However, if diag_ty is not a 'P' or an 'S' then both htfcode and lvadcode will show NULLS

Can help a bit more, but will need you to maybe show some sample rows for each of the tables and the expected results. Because as you can see above, there are a few different ways to interpret your requirements...

Thank you for the reply.  The CASE is giving me one vst_int_id.  I should be getting thousands of id's in the query.

OK, then suggest you try the example with the left outer joins rather than the case statements.

Could you please show us (make up) a few rows from each table and the expected output ?

Mark  

Here are the expected results of the query.  

vst_int_id          diag_ty          HtFCode          LVADcode
101                   P                    1245                NULL
104                   S                    NULL               9876
145                   P                    1246                NULL
175                   P                    1287                NULL
186                   S                    NULL               9834


Table 1

vst_int_id          diag_ty          diag_int_id
100                   P                    1254
101                   C                    1354
102                   S                    1675
103                   A                    9876
104                   P                    5437
105                   A                   6787

Table 2
diag_int_id          diag_code          diag_ds
1254                   9865                   LVAD
1004                   5698                   CHF
1456                   9845                   LVAD
9876                   1245                   HtF
5498                   1246                   HTF
6002                   3166                   MI9

Table 3

diag_code
1245
1256
1278
1345
1378
1476

Table 4
diag_code
9834
9876
9884
9887

I have tried your second solution and as you said I got a bunch of NULLS.  Hopefully the examples above will help.  

Thank you

with the given list of tables this will give you the result.

SELECT Table1.vst_int_id, Table1.diag_ty, Table2.diag_ds,Table3.HtFcode,
                CASE WHEN Table1.diag_ty = 'P' THEN Table3.diag_code
                     WHEN Table1.diag_ty = 'S' THEN Table4.diag_code
                     ELSE ISNULL(Table3.diag_code, Table4.diag_code)
                END AS LVADcode
FROM Table1 
                INNER JOIN Table2 ON Table1.diag_int_id = Table2. Diag_int_id 
                LEFT OUTER JOIN Table3 ON Table2.diag_code = Table3.diag_code 
                LEFT OUTER JOIN Table4 ON Table2.diag_code = Table4.diag_code

Select allOpen in new window