CODEWITHSUNDEEP
sqldb2
Randy
Randy

Reputation: 131

Pulling values from IBM DB2 lookup table based on relationship between 3 tables

I sincerely hope what I entered in the Title is not confusing. I also hope I explain this properly. In a nutshell, I have 3 tables as follows:

TABLE_A
id *
value

TABLE_B
id *
other_id

TABLE_C
other_id *
name_of_product

I want to pull multiple values from TABLE_A and one value from TABLE_C based on matching IDs between TABLE_A and TABLE_B, as well as matching ID between TABLE_B and TABLE_C. I have tried searching this, but haven't as yet found anything directly related to my problem. I have tried this SQL code, but I know it is wrong:

    SELECT
        TRIM(id)            primary_key_value, 
        a.value             name, 
        c.name_of_product   product 
    FROM   TABLE_A a, TABLE_C c 
    JOIN   TABLE_A t1 ON t1.id = a.id
    JOIN   TABLE_B t2 ON t2.other_id = c.other_id
    WHERE  c.name_of_product  = 'widget'

Any help would be greatly appreciated. If it isn't obvious by the code above, I should state that I am somewhat of an SQL newbie. Thank you.

Upvotes: 2

Views: 325

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

It seems you need two joins:

SELECT TRIM(a.id) as primary_key_value, 
       a.value as name, 
       c.name_of_product as product 
FROM TABLE_A a JOIN
     TABLE_B b
     ON b.id = a.id JOIN
     TABLE_C c
     ON c.other_id = b.other_id
WHERE c.name_of_product = 'widget'

Upvotes: 2

Related Questions