duplicates in query

[sgtpepper]

I am using the following mysql query

SELECT DISTINCT phplog_users.email,  phplog_users.active,  phplog_users.userid, phplog_users.datecreated,
phplog_userinput.inputfieldid,phplog_userinput.userid,phplog_userinput.value FROM phplog_users,phplog_userinput WHERE
phplog_users.userid = phplog_userinput.userid ORDER BY phplog_users.datecreated DESC";

My resutls show up like this:

145 - Created on 2005-09-05 - [e-mail] Deb Scully
145 - Created on 2005-09-05 - [e-mail] Deb Scully
145 - Created on 2005-09-05 - [e-mail] Deb Scully
145 - Created on 2005-09-05 - [e-mail] Deb Scully
146 - Created on 2005-09-05 - [e-mail] chris Scully
146 - Created on 2005-09-05 - [e-mail] chris clapp
146 - Created on 2005-09-05 - [e-mail] chris clapp
146 - Created on 2005-09-05 - [e-mail] chris clapp
146 - Created on 2005-09-05 - [e-mail] chris clapp

I do have multi instinses of the userid in the phplog_userinput table. How can I remove the duplicates? I am trying to get the following result

145 - Created on 2005-09-05 - [e-mail] Deb Scully
146 - Created on 2005-09-05 - [e-mail] chris Scully

Thanks!

[nemonoman]

You ARE getting distinct instances of the whole set of values in your query. To get the results you want, reduce the elements in the SELECT clause:

SELECT DISTINCT phplog_users.email, phplog_users.datecreated,
phplog_userinput.inputfieldid,phplog_userinput.userid FROM phplog_users,phplog_userinput WHERE
phplog_users.userid = phplog_userinput.userid ORDER BY phplog_users.datecreated DESC";

Guessing about the specific values you want.

You might also be getting a cartesian product if there are repeated values in phplog_users.userid

[sgtpepper]

With your query I get the following results.

150 - Created on 2005-09-05 - [e-mail] 
146 - Created on 2005-09-05 - [e-mail] 
151 - Created on 2005-09-05 - [e-mail] 
146 - Created on 2005-09-05 - [e-mail] 
151 - Created on 2005-09-05 - [e-mail] 
147 - Created on 2005-09-05 - [e-mail] 
151 - Created on 2005-09-05 - [e-mail] 
147 - Created on 2005-09-05 - [e-mail] 
151 - Created on 2005-09-05 - [e-mail] 
147 - Created on 2005-09-05 - [e-mail] 
151 - Created on 2005-09-05 - [e-mail] 
147 - Created on 2005-09-05 - [e-mail] 
152 - Created on 2005-09-05 - [e-mail] 
147 - Created on 2005-09-05 - [e-mail] 
152 - Created on 2005-09-05 - [e-mail] 
148 - Created on 2005-09-05 - [e-mail] 
152 - Created on 2005-09-05 - [e-mail] 
148 - Created on 2005-09-05 - [e-mail] 

It still is repeating.

[nemonoman]

Sergeant:
You are not revealing everything in your questions.

There are 2 possibilities:

Either you are including other criteria in your select statement that causes the returned row to be unique

OR

You have multiple values in your Joined table so that extra rows are being returned.

Example of first problem

First Middle Last
Joe A Blow
Joe B Blow

SELECT DISTINCT first, last
returns:
Joe Blow

SELECT DISTINCT first, middle, last
returns
Joe A Blow
Joe B Blow

Example of SECOND problem

UserTable
ID Name
1 Joe
2 Moe

NoteTable
ID UserID Text
1|1|Hi
2|2|Bye

SELECT DISTINCT UserTable.Name, NoteTable.Text
FROM UserTable, NoteTable
WHERE UserTable.ID=NoteTable.UserID

returns
Moe, Bye

BUT IF YOU MISTAKENLY ADD duplicate rows in the USER TABLE
UserTable
ID Name
1 Joe
2 Moe
2 Moe <<Note DUPs
2 Moe

The query will return:

Moe, Bye
Moe, Bye
Moe, Bye

So check your query and your data and don't be disingenuous.

[sgtpepper]

Sorry...I thought I made that clear in my first post when I said "I do have multi instinses of the userid in the phplog_userinput table. "

The phplog_userinput is structured with userid, inputfieldid, value.

[nemonoman]

In effect then, you are getting the results you asked for, but they are not the results you want. Doing a join like the one you've provided will only be satisfactory in a one-to-many table structure. Multiple repeated values (many-to-many relationship) in both tables means you will get multiple repeated results.