Select Distinct in Two Table Query

[casbboy]

I can't get this to work properly:


$gannouncement = mysql_query("SELECT DISTINCT comments.person_id, comments.comment_id, comments.party, people.person_id, people.name, people.type FROM comments JOIN people ON (comments.person_id = people.person_id) ORDER BY comments.comment_id DESC LIMIT 8",$connection);


There is no error but it isn't working. I want it to pull the DISTINCT rows in the comments.person_id column.

The first 8 rows that all have a different person_id in the comments table. Why isn't it working?

Thanks
Ryan

[mixerson]

I'm not an SQL expert, but no one has answered yet, so I'll give it a try...

How about using what I think is called a natural join? Something like:SELECT DISTINCT .... FROM comments, people WHERE comments.person_id = people.person_id ORDER BY ...

[casbboy]

the query worked but still didn't give unique rows.

Thanks
Ryan

[mixerson]

I think DISTINCT returns unique rows using all the columns you select - not just the first one. So if you want comments.person_id to be unique, then "SELECT DISTINCT comments.person_id FROM..." and see if that works.

Also, a few examples of your data records and query results would make it easier to figure out what's going on.