?
Solved

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

Posted on 2009-12-27
9
Medium Priority
?
214 Views
Last Modified: 2012-05-08
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  
0
Comment
Question by:johndeerb
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 1000 total points
ID: 26127188
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
 
LVL 26

Expert Comment

by:tigin44
ID: 26127202
is this what you are looking for
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26127211
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 26127376
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
 
LVL 4

Author Comment

by:johndeerb
ID: 26127398
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26127463
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
 
LVL 4

Author Comment

by:johndeerb
ID: 26127573
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 26127874
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
 
LVL 26

Expert Comment

by:tigin44
ID: 26128313
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question