MySQL: Joining 3 tables, LIMIT results by first table?

Question

I am selecting everything from prod_drop and joining 2 other tables for additional data. I do not want any of the rows from prod_drop to be repeated, I just want 1 row for each prod_drop row.

Here is my query:

SELECT 
    prod_drop.*
    , prod_drop_products.product_id
    , cart_product.product_image_sm 
FROM 
    prod_drop  
LEFT JOIN 
    prod_drop_products 
ON 
    prod_drop.prod_drop_id = prod_drop_products.prod_drop_id 
LEFT JOIN 
    cart_product 
ON 
    prod_drop_products.product_id = cart_product.product_id 
ORDER BY 
    prod_drop_id 
DESC

These are the results:

prod_drop_id    prod_drop_name prod_drop_available product_id product_image_sm
51  Carat Weight yes 4971 S5-3515Y_S.jpg
51  Carat Weight yes 4970 S5-3515Y_S.jpg
51  Carat Weight yes 4969 S5-3515Y_S.jpg
50  Carat Weight yes 4959 S5-3515_S.jpg
50  Carat Weight yes 4960 S5-3515_S.jpg
50  Carat Weight yes 4958 S5-3515_S.jpg
49  Metal Quality yes 3269 Q-8785X-14_S.jpg
49  Metal Quality yes 3270 Q-8785X-14_S.jpg
48  Gold Color yes 1635 1390-Y_S.jpg
48  Gold Color yes 1390 PE0048-12W_S.jpg

But I only want one row per prod_drop_id (doesn't matter which) so essentially I want my results to be like this:

prod_drop_id    prod_drop_name prod_drop_available product_id product_image_sm
51  Carat Weight yes 4971 S5-3515Y_S.jpg
50  Carat Weight yes 4959 S5-3515_S.jpg
49  Metal Quality yes 3269 Q-8785X-14_S.jpg
48  Gold Color yes 1635 1390-Y_S.jpg

How can I do that?

Thanks!

Answer 1

I guess I would try a MAX (or MIN) on the two other tables.

Answer 2

Assuming your duplicates are only in the cart_product table, try this:

SELECT pd.*, pdp.product_id, cp.product_image_sm 
FROM prod_drop pd
LEFT JOIN prod_drop_products pdp ON pd.prod_drop_id = pdp.prod_drop_id 
LEFT JOIN (
    select product_id, min(product_image_sm) as product_image_sm
    from cart_product
    group by product_id
) cp ON pdp.product_id = cp.product_id 
ORDER BY pd.prod_drop_id DESC

If there are possible duplicates in both tables, you'll need something like:

SELECT pd.*, pdp.product_id, cp.product_image_sm 
FROM prod_drop pd
LEFT JOIN (
    select prod_drop_id, min(product_id) as product_id
    from prod_drop_products
    group by prod_drop_id 
) pdp ON pd.prod_drop_id = pdp.prod_drop_id 
LEFT JOIN (
    select product_id, min(product_image_sm) as product_image_sm
    from cart_product
    group by product_id
) cp ON pdp.product_id = cp.product_id 
ORDER BY pd.prod_drop_id DESC

Answer 3

As you can see from your data, there are clearly more than one record in prod_drop_products for each record in prod_drop. So if you only want one row in the output for each distinct row in prod_drop, then you need to either eliminate the output columns from this table, or decide which of the multiple rows in prod_drop_products you want the output to get it's value from for the query output columns that are coming from this table ( product_id, specifically) If it doesn't matter which product_id you output (this doesn;t make sense from a business perspective, but hey...) then you can use Min, or max() whichever you prefer. If it does matter, then please specify what logic you want to use t omake that decision and the query can be edited t oreflect that...

Assuming Min() is ok, you can:

SELECT pd.prod_drop_id, pd.prod_drop_name, pd.prod_drop_available,     
  Min(pp.product_id), Min(cp.product_image_sm)
FROM prod_drop pd 
   LEFT JOIN prod_drop_products pp 
       ON pp.prod_drop_id = pd.prod_drop_id 
   LEFT JOIN cart_product cp
       ON cp.product_id = pp.product_id 
Group By pd.prod_drop_id, pd.prod_drop_name, pd.prod_drop_available
ORDER BY prod_drop_id DESC