DISTINCT not working? Or is it?

nelsons

Trying to pull a list of members, but only show a member once, not several times. Am still getting multiple instance of the same member. (the members info appears multiple times as a result of the query) Was under the impression that DISTINCT() would only return one each.

$sql = ("SELECT DISTINCT member.ID,
member.FirstName, 
member.LastName, 
member.City, 
member.State, 
jentries.ID_member, 
member.StartWeight,
jentries.DayWeight, 
member.StartDay
FROM 
member, jentries
WHERE member.ID = jentries.ID_member 
AND jentries.DayWeight < member.StartWeight 
AND jentries.DayWeight > 1 
AND member.StartWeight - jentries.DayWeight > 1 
ORDER BY jentries.DayWeight ASC");

Sxooter

You've probably got too many fields in the select distinct part. For instance, given this table struct:

id1|id2|id3

1|2|3
1|2|4
1|3|4
1|2|3

select distinct id1,id2,id3 will return all but the last row.

Build a simplified test case and post it (i.e. create table, insert statements, select statement) if this doesn't answer your question.

wedgeantilles

There are two ways to achieve your result.

Run a distinct query on member.ID,
member.FirstName, member.LastName and then run a new query using the results of that data.
Run the query as a whole, then when outputting the results, run a comparison check on member details when they are being displayed to check that they have not been outputted previously.