CODEWITHSUNDEEP
mysqlsqljoin
Rocky
Rocky

Reputation: 43

MySQL Queries with Meta Keys and Values

I have a hard time wrapping my head around coming up with a nice clean mysql query for this problem. I have two tables:

ORDER ITEMS                   ORDER ITEM META
-----------------             ---------------------
ID    Name                    ID    Key       Value   
-----------------             ---------------------
24    Product A               24    _qty      3
30    Product B               30    _qty      5
33    Product B               30    _weight   1000g
55    Product A               33    _qty      1
-----------------             33    _weight   500g
                              55    _qty      2
                              ---------------------

I ran this query:

SELECT
    oi.ID,
    oi.Name,
    oim1.Value as Qty,
    oim2.Value as Weight

FROM
   `order_items` as oi
   INNER JOIN `order_item_meta` as oim1 ON oim1.ID = oi.ID
   INNER JOIN `order_item_meta` as oim2 ON oim2.ID = oi.ID

WHERE
   oim1.Key = '_qty' AND
   oim2.Key = 'weight'

But it only gives me

-------------------------------
ID   Name         Qty    Weight
-------------------------------
30   Product B    5      1000g
33   Product B    1      500g
-------------------------------

I need to include products that do not have _weight defined as a key so it will give me the following results:

-------------------------------
ID   Name         Qty    Weight
-------------------------------
24   Product A    3
30   Product B    5      1000g
33   Product B    1      500g
55   Product A    2
-------------------------------

Upvotes: 3

Views: 980

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try using an outer join:

select oi.id, oi.name, oim1.value as qty, oim2.value as weight
  from order_items as oi
  join order_item_meta as oim1
    on oim1.id = oi.id
  left join order_item_meta as oim2
    on oim2.id = oi.id
   and oim2.key = '_weight'
 where oim1.key = '_qty'

Fiddle Test: http://sqlfiddle.com/#!2/dd3ad6/2/0

If there is ever a situation where an order doesn't have a quantity you would also have to use an outer join for the quantity, like this:

select oi.id, oi.name, oim1.value as qty, oim2.value as weight
  from order_items as oi
  left join order_item_meta as oim1
    on oim1.id = oi.id
   and oim1.key = '_qty'
  left join order_item_meta as oim2
    on oim2.id = oi.id
   and oim2.key = '_weight'

However if an order ALWAYS has an associated quantity (just not necessarily an associated weight) you should use the first query instead, an inner join for the quantity, and an outer join for the weight. (it all depends on your situation)

Upvotes: 1

Related Questions