Jump to content


Photo

**SOLVED** Error when using DISTINCT() with multiple tables


  • Please log in to reply
2 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 28 November 2005 - 03:56 PM

I'm referencing two tables in one query, but I only want to return distinct rows, heres what I have:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT(p.*) FROM products AS p, product_category AS pc WHERE $subs AND pc.productid=p.id AND p.featured='1' [!--sql2--][/div][!--sql3--]($subs just contains an extra bit of the query)
With this query, I'm getting the error:[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM products AS p, product_category AS pc WHERE (pc.catego[/quote]
As soon as I take out the DISTINCT(p.*) and replace it with just p.*, it works, but returns some duplicate rows... I'm obviously going wrong in my syntax above, anyone care to set me in the right direction?

#2 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 29 November 2005 - 01:56 AM

[!--quoteo(post=322761:date=Nov 28 2005, 11:56 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Nov 28 2005, 11:56 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I'm referencing two tables in one query, but I only want to return distinct rows, heres what I have:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT(p.*) FROM products AS p, product_category AS pc WHERE $subs AND pc.productid=p.id AND p.featured='1' [!--sql2--][/div][!--sql3--]($subs just contains an extra bit of the query)
With this query, I'm getting the error:
As soon as I take out the DISTINCT(p.*) and replace it with just p.*, it works, but returns some duplicate rows... I'm obviously going wrong in my syntax above, anyone care to set me in the right direction?
[/quote]

Hi SA,
I think that just removing the brackets around p.* should do:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT `p`.* FROM `products` AS `p`, `product_category` AS `pc` WHERE $subs AND `pc`.`productid` = `p`.`id` AND `p`.`featured` = '1' [!--sql2--][/div][!--sql3--]
Cook

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 29 November 2005 - 09:17 AM

[!--quoteo(post=322922:date=Nov 29 2005, 01:56 AM:name=Cook)--][div class=\'quotetop\']QUOTE(Cook @ Nov 29 2005, 01:56 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hi SA,
I think that just removing the brackets around p.* should do:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT `p`.* FROM `products` AS `p`, `product_category` AS `pc` WHERE $subs AND `pc`.`productid` = `p`.`id` AND `p`.`featured` = '1' [!--sql2--][/div][!--sql3--]
[/quote]
Spot on! Thanks Cook! :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users