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.

Nitin Sontakke

Sample data is always good to have...

Nitin Sontakke

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

Keng0499

ASKER

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

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Keng0499

ASKER

Thank.

BTW, what is c1 and c2?

Nitin Sontakke

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.

Keng0499

ASKER

Thanks. Finally got it working :)

HainKurt

you should select an answer and allocate points, not close it...

Keng0499

ASKER

I did select - it show Grade A on last screen - so how to redo

Keng0499

ASKER

redo