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

I am a newbie to sql. I am needing to show the vst_int_id's if the diag_ty='P' then use Table3 or if the diag_ty='S' then use Table4. I am not sure where to go from here to get the results that I need.

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 INNER JOIN
Table3.HtFcode ON Table2.diag_code = Table3.HtFcode INNER JOIN
Table4.LVADcode ON
Table2.diag_code = Table4.LVADcode  
LVL 4
johndeerbAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
what do you mean by using table3 or table4? do you want to display some columns or the whole table? do the columns of the table3 and table4 are identical?
0
 
tigin44Commented:
is this what you are looking for
0
 
tigin44Commented:
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

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Mark WillsTopic AdvisorCommented:
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...
0
 
johndeerbAuthor Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
johndeerbAuthor Commented:
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

0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well those samples above dont quite match up, but it does help all the same...

select t1.vst_int_id, t1.diag_ty, t3.diag_code as htfcode, t4.diag_code as LVADcode
from table1 t1
inner join table2 t2 on t1.diag_int_id = t2.diag_int_id
left outer join table3 t3 on t2.diag_code = t3.diag_code and t1.diag_ty = 'P'
left outer join table4 t4 on t2.diag_code = t4.diag_code and t1.diag_ty = 'S'
Where t1.diag_ty in ('P','S')
and (t3.diag_code is not NULL or t4.diag_code is not NULL)
0
 
tigin44Commented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.