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

[zq29]

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

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?

[Cook]

[!--quoteo(post=322761:date=Nov 28 2005, 11:56 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Nov 28 2005, 11:56 PM) 322761[/snapback][/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?

 

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--]

[zq29]

[!--quoteo(post=322922:date=Nov 29 2005, 01:56 AM:name=Cook)--][div class=\'quotetop\']QUOTE(Cook @ Nov 29 2005, 01:56 AM) 322922[/snapback][/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--]

Spot on! Thanks Cook!