[SOLVED] Not sure if this is possible!

[Bricktop]

Hi there,

 

I have a table named "users", contained within it are id, firstname, lastname, email, notes.

 

However, some people appear in this table more than once so I use the DISTINCT opearator to stop these from appearing.

 

But, what I would like to do is to perform a further query on the data returned.  So for example the code:

 

$sql = mysql_query("SELECT DISTINCT firstname, lastname, email, notes FROM users ORDER BY lastname ASC");

 

will produce let's say 50 results.  Of these results I then want to:

 

$sql = mysql_query("SELECT id FROM the_above_output");

 

What I tried was:

 

$sql = mysql_query("(SELECT DISTINCT firstname, lastname, email, notes FROM users ORDER BY surname ASC) UNION (SELECT id FROM users )");

 

But I didn't fully understand the UNION statement so obviously the above didn't work (you can't have a different number of coulmns for each SELECT statement and even if you could the above wouldn't have produced the desired effect).

 

I hope I've made some sense, like I said not sure if the above is possible but if anyone knows of a way it would be much appreciated.  Perhaps you can DISTINCT one column but do a normal select on the others somehow?  Then I can DISTINCT just the surname and do a normal select on everything else.

 

Thanks in advance.

[dzelenika]

I'm not sure you're on right way.

If you want to find duplicate entries then use HAVING clause:

SELECT id FROM USER WHERE (firstname, lastname) IN(
SELECT firstname, lastname  FROM user
GROUP BY firstname, lastname
HAVING COUNT(*) > 1)

[Bricktop]

Hi, thanks for getting back to me - the above code just outputs 4 id's, (6, 7, 10 and 11).  Also, the above won't allow me to get the other data - email address and notes.

 

Any other ideas?

 

Thanks

[dzelenika]

I don't understand following:

 

Perhaps you can DISTINCT one column but do a normal select on the others

 

If you show one name for example JOHN.

How do you can show other data for each JOHN?

[Bricktop]

OK, so I would like to DISTINCT the lastname.  So only one person with the last name of "smith" will be shown.

 

Once I have these results, I would then like to display the id, firstname, lastname, notes, email address of those outputted results.

[dzelenika]

OK, so I would like to DISTINCT the lastname.  So only one person with the last name of "smith" will be shown.

 

Once I have these results, I would then like to display the id, firstname, lastname, notes, email address of those outputted results.

 

If there are three persons called "smith", do you want to see data of first, second or third smith?

[Bricktop]

Hi, sorry I should have said, where there are duplicates it does not matter whose data gets displayed - any is fine.

[dzelenika]

Hi, sorry I should have said, where there are duplicates it does not matter whose data gets displayed - any is fine.

then you can write (only in mysql, for other db this doesn't work)

 

SELECT * FROM user GROUP BY firstname, lastname

[Bricktop]

Thank you so much dzelenika, such a simple solution!  Worked perfectly for what I needed.

 

Thanks again.