CODEWITHSUNDEEP
javamysqlsql
Praveen
Praveen

Reputation: 17

Query on Joins - Each column is a key in other tables. Need to join and fetch the corresponding column value

I am having a problem framing a query with joins. Below you can find my tables structure and data.

Tabl1 (Contains ID's and Code's. The description for these codes is present in tabl3.)

ID1 ID2 CD1 CD2
1 101 MAC BMW
1 102 XPS AUD

Tabl2 (Contains Customer names)

tab_id1 tab_id2 Cust_Name
1 101 Cust Name 1
1 102 Cust Name 2

Tabl3 (Contains Description of codes present in tabl1.)

tab_id1 Code Description
1 MAC Apple laptop
1 XPS Dell laptop
1 BMW BMW Auto
1 AUD Audi Auto

Tabl1 holds the codes in the columns cd1, cd2. For these codes the description is defined in tabl3. So I need to join tabl3 and get the description values and display it. So I need to write a query that should have the following output.

id1, id2, cust_name, cd1 ||' - '|| description for cd1, cd2 || ' - ' ||description for cd2.

I tried the below query, but I'm getting multiple rows (4 rows whereas the output should have only 2 rows).

select
    t1.id1,
    t1.id2,
    t2.cust_name,
    t1.cd1,
    t3.description,
    t1.cd2,
    t3.description
from
    tabl1 t1,
    tabl2 t2,
    tabl3 t3
where
    t1.id2 = t2.tab_id2
    AND t1.id1 = t3.tab_id1
    AND t1.id1 = t2.tab_id1
    AND (
        t3.code = t1.cd1
        or t3.code = t1.cd2
    );

Upvotes: 0

Views: 123

Answers (1)

eshirvana
eshirvana

Reputation: 24568

the last join with table3 makes two rows for each match in code , you need to do join with table3 two times , eachtime with code 1 and code 2:

select
    t1.id1,
    t1.id2,
    t2.cust_name,
    t1.cd1,
    t3_1.description,
    t1.cd2,
    t3_1.description
from
    tabl1 t1
join tabl2 t2 
    on t1.id2 = t2.tab_id2
    AND t1.id1 = t2.tab_id1
join tabl3 t3_1 
    on t1.id1 = t3_1.tab_id1
    AND t3_1.code = t1.cd1
join tabl3 t3_2
    on t1.id1 = t3_2.tab_id1
    AND t3_2.code = t1.cd2

Upvotes: 1

Related Questions