How to display only first product among products with same pkid and color using MySQL query

Question

SQL Query

SELECT DISTINCT a.*, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
n.title AS sizetitle 
FROM t_store a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES' 
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' 
AND a.catpkid = 5 
ORDER BY a.createdatetime DESC

SQL Query Result

pkid    catpkid subcatpkid  title       description     createdatetime          visible     listprice   ourprice    memberprice     subcattitle largephotofilename  cattitle    colortitle  sizetitle   
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        L
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        XL
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        XXL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        L
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XXL
135     5       118         Title02     Desc02          2020-07-15 16:26:52     YES         1599.00     570.00      470.00          SubCat01    image002.jpeg       cat001      Pink        XXXL

I want to make so only first product with same pkid and color should display in product listing. Currently they are displaying as 3 different products because they have same pkid and color but different sizes.

I tried to use DISTINCT with colortitle field but it is not working properly. So what changes do I need to make in query to display only first product instead of 3 products.

UPDATED QUERY

SELECT a.pkid, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
group_concat(n.title ) AS sizetitle 
FROM t_store a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES' 
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' 
AND a.catpkid = 5 
GROUP BY a.pkid
ORDER BY a.createdatetime DESC

UPDATED MYSQL QUERY RESULT

pkid    catpkid subcatpkid  title       description     createdatetime          visible     listprice   ourprice    memberprice     subcattitle largephotofilename  cattitle    colortitle  sizetitle   
132     5       118         Title01     Desc01          2020-07-15 14:44:51     YES         1599.00     570.00      470.00          SubCat01    image001.jpeg       cat001      Gray        L,L,L,L,XL,XL,XL,XL,XXL,XXL,XXL,XXL

Answer 1

You have different size so DISTINCT can't return a single row for color and pkid if the size is not important you could try using an aggregation function eg as group_concat that return all the size in a single row

SELECT  a.pkid, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
group_concat(n.title ) AS sizetitle 
FROM t_store a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES' 
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' 
AND a.catpkid = 5 
GROUP BY a.pkid, b.subcattitle, c.largephotofilename,  d.title , f.title 
ORDER BY a.createdatetime DESC

or a min() or max()

SELECT a.pkid, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
min(n.title ) AS sizetitle 
FROM t_store a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES' 
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' 
AND a.catpkid = 5 
GROUP BY a.pkid, b.subcattitle, c.largephotofilename,  d.title , f.title 
ORDER BY a.createdatetime DESC

anyway if you use group by you can't use the select all operator *, you must explicitally declare in select clause the column name you need not involved by aggreagtion and repated these column in group by clause in my sample i have used onl a.pkid

Answer 2

Let me know if this works, its good if you provide details of table structure and some sample data. This approach is using row_number() which gives you the number for each rows and we are using over(partition by pkid) which would start the series of sequence number unless there is a change in pkid, if there is a change it again starts with 1 till it finds another change. So my idea is to filter out the 1st one from them and then join them out. So your table t_store a has this functions in an inline view.

(select *,row_number() over(partition by pkid) as rn from t_store) a

Suggested change :

SELECT DISTINCT a.*, 
b.subcattitle, 
c.largephotofilename,  
d.title AS cattitle, 
f.title AS colortitle, 
n.title AS sizetitle 
FROM (select *,row_number() over(partition by pkid) as rn from t_store) a 
LEFT JOIN t_store_cat d ON a.catpkid=d.catpkid AND d.visible='YES'  and a.rn=1
LEFT JOIN t_store_subcat b ON a.subcatpkid=b.subcatpkid 
LEFT JOIN tr_store_photo c ON a.pkid=c.masterpkid 
LEFT JOIN tr_store_color e ON a.pkid=e.itempkid 
LEFT JOIN t_store_color f ON e.masterpkid=f.pkid 
LEFT JOIN tr_store_size m ON a.pkid=m.itempkid 
LEFT JOIN t_store_size n ON m.masterpkid=n.pkid 
WHERE a.visible='YES' AND b.visible='YES' AND c.visible='YES' and a.rn=1
AND a.catpkid = 5 
ORDER BY a.createdatetime DESC