MySQL: Select a separate field, but do you get the rest of the columns?

advertisements

I want to select distinct product_series, but I want all the other columns too.

This is my query as it stands:

SELECT DISTINCT product_series
FROM cart_product
WHERE product_brand = "everlon"
AND product_type = "ring"
AND product_available = "yes"

This only gives me product_series, I need all the other columns in that row too. If I try to select more than just product_series, I end up getting multiples of product series.

I want is * all the fields but I want to limit it so I only get 1 row per product series.

I am not sure if I am explaining this correctly so let me give an example:

if I have

product_series   product_id
----------------------------
"seriesA"        230
"seriesA"        231
"seriesB"        232
"seriesB"        233

I would get all the columns but only 1 per product_series:

product_series   product_id
----------------------------
"seriesA"        230
"seriesB"        232

How can I do that?


SELECT  pi.*
FROM    (
        SELECT  DISTINCT product_series
        FROM    cart_product
        ) pd
JOIN    cart_product  pi
ON      pi.id =
        (
        SELECT  id
        FROM    cart_product po
        WHERE   product_brand = "everlon"
                AND product_type = "ring"
                AND product_available = "yes"
                AND po.product_series = pd.product_series
        LIMIT 1
        )

This will pick one product per series in no particular order.

Add an ORDER BY condition into the subquery to define the order.

You may also want to read this article in my blog: