Mysql select 2 rows from each group
I have 2 tables with this structure
Products
id title
-----------------
1 sample 1
2 sample 2
3 sample 3
4 sample 4
5 sample 5
6 sample 6
Gallery
id typeid name
-------------------------------
1 1 sample for 1
2 1 sample for 1
3 1 sample for 1
4 2 sample for 2
5 2 sample for 2
7 2 sample for 2
8 3 sample for 3
9 3 sample for 3
10 3 sample for 3
11 4 sample for 4
12 4 sample for 4
13 5 sample for 5
14 5 sample for 5
and this is needed for lists id eg (1,2,3)
id typeid name
---------------------
1 1 sample for 1
1 2 sample for 1
2 4 sample for 2
2 5 sample for 2
3 8 sample for 3
3 9 sample for 3
here is my query
select p. *, g. * from products p inner join gallery g ON p.id = g.typeid where p.id in (3,4,5) group by typeid
there is a real structure here sqlfiddle link
+3
saman khademi
source
to share
2 answers
SELECT p.id, g.typeid, g.id, g.title
FROM products p
INNER JOIN (SELECT * FROM gallery a
WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2
) g ON p.id = g.typeid
WHERE p.id in (3,4,5)
EDIT:
Try SQL Fiddle Demo
SELECT p.id, g.typeid, g.id, g.name
FROM products p
INNER JOIN (SELECT * FROM gallery a
WHERE (SELECT COUNT(*) FROM gallery b WHERE b.typeid = a.typeid AND b.id >= a.id) <= 2
) g ON p.id = g.typeid
WHERE p.id in (3,4,5) order by g.id asc
So basically this part
WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2
used to replace group by typeid
+4
RRayasa
source
to share
SELECT p.id, p.title, g.typeid, g.id, g.name
FROM products p
INNER JOIN (SELECT * FROM gallery a
WHERE (SELECT COUNT (*) FROM gallery b WHERE b.typeid = a.typeid AND b.id> = a.id)
try it
+2
Ehsan Aghaei
source
to share