CODEWITHSUNDEEP
mysqlsql
Guerra
Guerra

Reputation: 2790

Mysql Multi Group By

I have a product table and it's have a product_id, And i have other table it's have like a group. Lets say:

Product table(product):
ID  NAME
1 - Shoes
2 - T-shirt
3 - apple
4 - any thing
5 - lorem ipsum
_______________________________________________
Group product table(g_product):
ID   -   GROUP ID    -   PRODUCT ID
1            1               1
2            1               2
3            1               5

I need a query to get just 1 product from each group. I've tried this:

select * from product as p
left join g_product as g on p.id = g.fk_prod
group by group_id

But this bring me JUST the grouped products.

I wan't this result:

1 - Shoes
3 - apple
4 - any thing

That's the product 1 from the group 1, and the 3 and 4 haven't group.

I figure it to work with this query:

select * from product as p
left join g_product as g on p.id = g.fk_prod
group by g.group_id
union
select * from product as p
left join g_product as g on p.id != g.fk_prod
group by p.id

But this needs 30 seconds to run, and it's much. I know have a way to do this fast. But i can't figure

Upvotes: 1

Views: 85

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

Here is my final version :)

select * from products
where id in (
    select p.id from products p
    left join g_product g on g.product_id = p.id
    group by g.prod_id
)

Note you will get ANY p.id if you group by without a an aggregate function such as min(p.id) but that will be ANY p.id in the g.prod_id group, which you've specified that was OK. If you add more than one field, such as p.id, p.name then you will get unpredictable results as the documentation states in this link:

http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html

Upvotes: 0

Rakesh Soni
Rakesh Soni

Reputation: 10887

Use this query to fetch appropriate result 

SELECT p.*, 
(CASE WHEN  g.gid IS NOT NULL THEN g.gid ELSE -1*p.id END) AS temp_group_id
FROM product AS p
LEFT JOIN g_product AS g ON p.id = g.fk_prod
GROUP BY temp_group_id

Here if a product not belong to any group then we are assigning a dummy group id (-1*p.id) to it. and that make the trick

Upvotes: 1

Related Questions