PHP/ mySQL joins - muliple duplicated rows in result

Question

In a wordpress based booking system I am trying to find the number of Adults that are going to all events for all time - this info is stored in the wp_woocommerce_order_itemmeta table (e.g. meta_key='Adults', meta_value='6') . In phpmyadmin I can see in the wp_woocommerce_order_itemmeta table there are 20 rows which have a meta_key value of 'Adults' (ie 20 total bookings). But when I run this code rather than printing 20 lines, each line gets repeated approx 42 times, so i end up with 840 lines (or so)

e.g.

meta_key Adults meta_value 6 meta_key Adults meta_value 6 meta_key Adults meta_value 6 meta_key Adults meta_value 6 meta_key Adults meta_value 6 meta_key Adults meta_value 6 meta_key Adul .. and so on

rather than:

meta_key Adults meta_value 6 meta_key Adults meta_value 8 meta_key Adults meta_value 11 meta_key Adults meta_value 1 meta_key Adults meta_value 1 meta_key Adults meta_value 2 .. and so on up to the desired 20 pairs of results

This is the code:

global $wpdb;
$results= $wpdb->get_results( '
select wp_woocommerce_order_itemmeta.meta_key, wp_woocommerce_order_itemmeta.meta_value
FROM wp_postmeta
inner JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.ID
inner JOIN wp_woocommerce_order_items
ON wp_woocommerce_order_items.order_id = wp_posts.ID
inner JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
where 1=1
AND wp_woocommerce_order_itemmeta.meta_key ="Adults"
', OBJECT );
foreach ($results as $res2) {
foreach ($res2 as $key => $value) {
            echo "$key $value\n";
            }
}

If I add DISTINCT to the SELECT then I lose rows where the number of adults going is the same, so that's not what I want. I just want to print those 20 rows that exist then I can add up the number of Adults going to all events for all time.

I need the joins in there as later I will first have to check that a booking is complete (which is in the wp_posts table), and I need to relate the post ID to the order_ID to relate that, etc etc

Where is my logic failing at this early stage? Thanks for any help.

You can't just join one-to-many because the one part will get repeated and you'll get multiple of these results. You will need to do separate queries and then join the data together. — Andrius
is that right? i thought you could do pretty much anything in one query if it was written right.. just after I posted this I tried adding select DISTINCT wp_posts.ID, at the front of the query and I can then get only the desired rows as the posts ID is unique.. seems to work although I would like some pointers on how to join the data from separate queries together as I might need to do that way.. I know you have to use multiple (duplicated) loins with aliases to search for stuff twice.. — Shaun Stevens

Anton Ohorodnyk Anton Ohorodnyk · Accepted Answer · 2015-11-12T13:38:28

I not sure that I fully understand your SQL, but can you use LEFT JOIN instead INNER JOIN?

select wp_woocommerce_order_itemmeta.meta_key, wp_woocommerce_order_itemmeta.meta_value
FROM wp_postmeta
LEFT JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.ID
LEFT JOIN wp_woocommerce_order_items
ON wp_woocommerce_order_items.order_id = wp_posts.ID
LEFT JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
where 1=1
AND wp_woocommerce_order_itemmeta.meta_key ="Adults"

I think it's will help you. Also read please different between JOINs.

If it willn't help get except result, you can try goup your results by GROUP BY

select wp_woocommerce_order_itemmeta.meta_key, wp_woocommerce_order_itemmeta.meta_value
FROM wp_postmeta
inner JOIN wp_posts
ON wp_postmeta.post_id = wp_posts.ID
inner JOIN wp_woocommerce_order_items
ON wp_woocommerce_order_items.order_id = wp_posts.ID
inner JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
where 1=1
AND wp_woocommerce_order_itemmeta.meta_key ="Adults"
GROUP BY wp_posts.ID

PHP/ mySQL joins - muliple duplicated rows in result

1 Answers