I want to Select join with cats
table with data
. Join with cats.id = data.cat_id
. with limit 3 data
table row for each unique category id cat.id
Table: cats
id | parent_id | name ---+-----------+-------- 1 | 0 | Games 2 | 0 | Music 3 | 0 | Apps
Table:data
-------------------------- id | cat_id | data ---+---------+------------ 1 | 1 | Cat1 Data1 2 | 1 | Cat1 Data2 3 | 1 | Cat1 Data3 4 | 1 | Cat1 Data4 ---+---------+------------ 5 | 2 | Cat2 Data1 6 | 2 | Cat2 Data2 7 | 2 | Cat2 Data3 8 | 2 | Cat3 Data4 ---+---------+------------ 9 | 3 | Cat3 Data1 10 | 3 | Cat3 Data2 11 | 3 | Cat3 Data3 12 | 3 | Cat3 Data4
I want like bellow. For one cats
per row select three data
row select.
category | data ----------+------------ Games | Cat1 Data1 Games | Cat1 Data2 Games | Cat1 Data3 ----------+------------ Music | Cat2 Data1 Music | Cat2 Data2 Music | Cat2 Data3 ----------+------------ Apps | Cat3 Data1 Apps | Cat3 Data2 Apps | Cat3 Data3
if this not posiable group_concat
with ,
also work for me. I will split it with php.
cat_name | data ----------+------------------------------------- Book | Cat1 Data1, Cat1 Data2, Cat1 Data3 Music | Cat2 Data1, Cat2 Data2, Cat2 Data3 Game | Cat3 Data1, Cat3 Data2, Cat3 Data3
Advertisement
Answer
Hope this solve your
SELECT sq.category, sq.data FROM ( SELECT DATA.*, cats.name AS category, row_number() over(PARTITION BY cat_id ORDER BY cat_id DESC) AS ROW_COUNT FROM `data` INNER JOIN cats ON data.cat_id = cats.id ) AS sq WHERE ROW_COUNT <= 3
here in the query … … ROW_COUNT <= 3 you may set limit for selection.