Link to home
Start Free TrialLog in
Avatar of Keng0499
Keng0499Flag for United States of America

asked on

IF SQL Query

To the expert,

Need some help.

I have 2 tables:

Table1: prod_name, prod_type, colorcode
Table2: code_desc, code1, code2

I need to select all the record from Table1 where

prod_type = 0 then based on code1, display code_desc
prod_type=1 then based on code2, display code_desc

How do I create the SQL Query?

Thanks in advanced.
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Sample data is always good to have...
Hopefully something like this...however, it is not quite clear if colorcode matches only with 1 record or 2 records and you want both.

select prod_name, prod_type, colorcode, c1.code_desc, c2.code_desc
from table1 t1
left outer join table2 c1 on t1.colorcode = c1.code1
left outer join table2 c2 on t1.colorcode = c2.code2

Open in new window

Avatar of Keng0499


prod_name, prod_type, colorcode
BrandA, 0, 98210223
BrandB, 0, 28934423
BrandC, 1, 29020192

code_desc, code1, code2
A_123, 98210223, 27832333-1
B_XYZ, 28934423, 92081245-T
PO30923, 82739344-XX, 29020192
Avatar of HainKurt
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

BTW, what is c1 and c2?
They are called table alias (just as there are column alias). We are using same table twice. Different table alias allow us to reference one table twice as if there are two different tables.
Thanks. Finally got it working :)
you should select an answer and allocate points, not close it...
I did select - it show Grade A on last screen - so how to redo