Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: duplicate ids not returned

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    21
    Thanx a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •