Innie or Outie? JOIN that is

Databases
1

I wrestled with this query this weekend without much success. Any guidance would be greatly appreciated.

table_list
id | name
1 | spec name one
2 | spec name two
3 | spec name three

table_specs
id | list_item_id | inventory_item_id | value

1 | 2 | 181 | 100 gal

I need two different queries.

Query 1: return the entire table_list (all entries) Joined with table_specs ON table_specs.list_item_id = table_list.id AND table_specs.inventory_item_id = (whatever inventory item I’m looking at). This would give me a complete list of all possible specs but with some having empty values.

Query 2: return ONLY the table_list items that have corresponding Joined values in the table_specs table for a certain inventory item.

I’ve tried INNER JOIN, and several variations of OUTER’s, at this point I’ve confused myself greatly… sigh. Any guidance would be greatly appreciated.

Thanks in advance for any guidance or suggestions!

2

Here is the code as it stands. This only returns a list that has values in the table_specs.


SELECT 
       table_list.id
     , table_list.name
     , table_specs.id AS unique_spec_value
     , table_specs.list_item_id 
     , table_specs.inventory_item_id
     , table_specs.value

FROM table_list

LEFT OUTER JOIN table_specs
    ON table_list.id = table_specs.list_item_id

WHERE table_specs.inventory_item_id = 199

btw, my copy of Rudy’s book was at home this weekend and I was at my girlfriends place all weekend so normally I would reference it for this type of problem.

3 

SELECT 
       table_list.id
     , table_list.name
     , table_specs.id AS unique_spec_value
     , table_specs.list_item_id 
     , table_specs.inventory_item_id
     , table_specs.value

FROM table_list

LEFT OUTER JOIN table_specs
    ON table_list.id = table_specs.list_item_id

and table_specs.inventory_item_id = 199
4

AWESOME! That did it. For those of you that can’t pick out the change,

The WHERE was changed to an AND

Thanks!!

5

When you do an OUTER JOIN and place a condition on the right hand table, that condition belongs in the join itself. Putting it in the where clause like you did filtered out the non matching rows, essentially creating an INNER JOIN.

Thought you’d want to know that for future reference.

6

Yup, it all made sense right when I saw the AND. Had one of those ‘why didn’t I think of that’ moments.