My SQL query within a query

Question

I have 2 tables that I am trying to combine in a specific way Table 1: ‘part_defs’ Table 2 Items_part_values

in value_defs:

ID  | Name
-------------
  1 | color
  2 | size
  3 | weight

in Items_part_values

ItemID | valueID | Value
-------------------------
    10 | 1       | red
    11 | 1       | blue

What I need is a query where for a given item all the rows from value_defs appear and if they have a value in Items_part_values the value.

So for Item 11 I want

ID | Name   | Value
--------------------
 1 | color  | red
 2 | size   | NULL
 3 | weight | NULL

I’m new to MySQL, in access I would have created a subquery with the ItemID as a parameter and then done a Left Join with value_defs on the result.

Is there a way of doing something similar in MySQL?

Thanks

Answer 1

Use a left join:

SELECT * FROM Table1 LEFT JOIN Table2 USING (ID);

Edit:

SELECT * FROM part_defs LEFT JOIN Items_part_values ON part_defs.ID = Items_part_values.valueID;

Answer 2

Use:

   SELECT p.id,
          p.name,
          ipv.value
     FROM PART_DEFS p
LEFT JOIN ITEMS_PART_VALUES ipv ON ipv.valueid = p.id
                               AND ipv.itemid = ?

Replace the "?" with the itemid you want to search for.
This means all the PARTS_DEF rows will be returned, and if the ITEMS_PART_VALUES.valueid matches the PART_DEFS.id value, then the ITEMS_PART_VALUES.value value will be displayed for the item you are looking for. If there's no match, the value column will be NULL for that record.

There's a difference in OUTER JOINs, when specifying criteria in the JOIN vs the WHERE clause. In the JOIN, the criteria is applied before the JOIN occurs while in the WHERE clause the criteria is applied after the JOIN.