no matching row in join table nulling the whole row

Question

No error messages just lost rows. I should have 27 rows. I lose 16 rows to a missing _shipping company row and 6 rows to the missing _shipping_address_2 row.

SELECT p.post_id, 
       FROM_ UNIXTIME(p.meta_value) AS ShipDate, 
       t1.meta_value AS CustomerFName, 
       t2.meta_value AS CustomerLName, 
       ifnull(t3.meta_value, '') AS CompanyName, 
       t4.meta_value AS AddressLine1, 
       ifnull(t5.meta_value, '') AS AddressLine2, 
       t6.meta_value AS City, 
       t7.meta_value AS State, 
       t8.meta_value AS Zip, 
       t9.meta_value AS Tax, 
       t10.meta_value AS shippingTax 

FROM    wsuwp.wp_1175_postmeta p 
LEFT JOIN wp_1175_postmeta t1 ON p.post_id = t1.post_id 
LEFT JOIN wp_1175_postmeta t2 ON p.post_id = t2.post_id 
LEFT JOIN wp_1175_postmeta t3 ON p.post_id = t3.post_id
LEFT JOIN wp_1175_postmeta t4 ON p.post_id = t4.post_id
LEFT JOIN wp_1175_postmeta t5 ON p.post_id = t5.post_id 
LEFT JOIN wp_1175_postmeta t6 ON p.post_id = t6.post_id 
LEFT JOIN wp_1175_postmeta t7 ON p.post_id = t7.post_id
LEFT JOIN wp_1175_postmeta t8 ON p.post_id = t8.post_id 
LEFT JOIN wp_1175_postmeta t9 ON p.post_id = t9.post_id 
LEFT JOIN wp_1175_postmeta t10 ON p.post_id = t10.post_id 

WHERE (p.post_id IN (SELECT ID FROM wp_1175_posts 
                     WHERE post_type = 'shop_order' AND post_status = 'wc-completed')
  AND  p.meta_key = '_date_completed'
  AND  p.meta_value BETWEEN UNIX_TIMESTAMP('2019-05-01 00:00:00') 
                        AND UNIX_TIMESTAMP('2019-06-01 00:00:00') )
  AND t1.meta_key = '_shipping_first_name' 
  AND t2.meta_key = '_shipping_last_name'
  AND t3.meta_key = '_shipping_company'
  AND t4.meta_key = '_shipping_address_1' 
  AND t5.meta_key = '_shipping_address_2' 
  AND t6.meta_key = '_shipping_city' 
  AND t7.meta_key = '_shipping_state' 
  AND t8.meta_key = '_shipping_postcode'
  AND t9.meta_key = '_order_tax' 
  AND t10.meta_key = '_order_shipping_tax' 

GROUP BY p.post_id;

Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jun 06 '19 at 22:15
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 06 '19 at 22:15
Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS Read about usually-an-antipattern EAV & straightforward relational DB design, if you have control over your schema. — philipxy, Jun 06 '19 at 22:17

Gordon Linoff · Accepted Answer · 2019-06-06T19:40:53.420

Use conditional aggregation. Something like:

SELECT p.post_id,
       MAX(CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.value END) as shipping_first_name,
       MAX(CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.value END) as shipping_last_name,
       . . . -- continue for the rest of the columns
FROM  wsuwp.wp_1175_postmeta p JOIN
      wp_1175_postmeta pm
      ON p.post_id = pm.post_id 
WHERE p.post_id IN (SELECT pp.ID
                    FROM wp_1175_posts pp
                    WHERE pp.post_type = 'shop_order' AND pp.post_status = 'wc-completed'
                   ) AND
      p.meta_key = '_date_completed'
      p.meta_value BETWEEN UNIX_TIMESTAMP('2019-05-01') AND UNIX_TIMESTAMP('2019-06-01')
GROUP BY p.post_id;

Thank you that's awesome. I had tried the case but wrapping it in the aggregate never occurred to me. — Jeff, Jun 06 '19 at 19:10

no matching row in join table nulling the whole row

1 Answers1