SELECT * FROM #__vm_product AS prd INNER JOIN #__vmfprodsubcategory AS feat ON feat.product_id=prd.product_id INNER JOIN #__vm_product_category_xref AS cat ON cat.product_id=prd.product_id WHERE prd.product_id IN (1,5,5,6) AND cat.category_id=1 GROUP BY prd.product_id
As you have seen there is a
WHERE prd.product_id IN (1,5,5,6)
where some ids are being reffered more than 1 times.
But the query returns them once. The above query returns the product ids 1,5,6.
I want them to be returned as much times as there are in the where in clause for ordering them with a count function.
FROM ( SELECT 1 AS id
SELECT 6 ) AS xxx
JOIN #__vm_product AS prd
ON prd.product_id = xxx.id
JOIN #__vmfprodsubcategory AS feat
ON feat.product_id = prd.product_id
JOIN #__vm_product_category_xref AS cat
ON cat.product_id = prd.product_id
AND cat.category_id = 1
i removed your GROUP BY clause because clearly, GROUP BY is totally wrong if used with the dreaded, evil "select star"