duplicate ids not returned

sakis · #1 (**permalink**) 12-07-09, 09:37

Hi i am having a query such as

Code:

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

Code:

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.

Is this possible?

r937 · #2 (**permalink**) 12-07-09, 10:18

Code:

SELECT * 
  FROM ( SELECT 1 AS id
         UNION ALL
         SELECT 5
         UNION ALL
         SELECT 5
         UNION ALL
         SELECT 6 ) AS xxx
INNER
  JOIN #__vm_product AS prd 
    ON prd.product_id = xxx.id
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 
   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"

sakis · #3 (**permalink**) 12-14-09, 13:52

Thanx a lot

Internet Services

Web Development

Digital Marketing

Consumer Tech