Select Disticnt - Help Me Optimize this Query

[kkffjjman]

I'm wondering if this is set up in the best way. Basically I need contact_email, contact_name, contact_id, but no duplicates for contact_email ONLY. IE - I WANT the record if it has the same NAME as another, but has a different email...and so on...

msql = "SELECT distinct contact_email, min(contact_name) as contact_name, min(contact_id) as contact_id FROM db_contact WHERE contact_comment=TRUE and contact_form_page = '/folder/pagename.asp' group by contact_email"

 

[randy700]

I don't think you want the minimum values of contact_name and contact_id. The DISTINCT parameter covers the entire selection.

Sample data:
Email Name ID
johnsmith@tektips.com John Smith 437
johnsmith@yahoo.com John Smith 624
larryjones@yahoo.com Larry Jones 108
thepres@google.com George Bush 200
larryjones@yahoo.com Larry Jones 109

SELECT Distinct contact_email, contact_name, contact_id
FROM db_contact
WHERE contact_comment=TRUE and contact_form_page= '/folder/pagename.asp'

Assuming the WHERE condition is met, should yield these results:
Email Name ID
johnsmith@tektips.com John Smith 437
johnsmith@yahoo.com John Smith 624
larryjones@yahoo.com Larry Jones 108
thepres@google.com George Bush 200


Randy

 

[kkffjjman]

Thats interesting, I got back both values when i used that SQL (thats the sql i used in my first try)

I read up on it, and it said that because the SQL saw the ROW as different based on the three columns, it returned both.

So, your example was returning both of those larryjones@yahoo.com rows.

I wonder if that has anything to do with the cursor type i used to open/execute the connection?

 

[BigRed1212]

So:

If John Smith is in the database twice with one email, you want him 1 time?

If John Smith is in the database twice with two emails you want him twice, once for each email?

If Larry Smith and John Smith (brothers that share a computer) are both in the database and have the same email, what do you want returned?

 

[kkffjjman]

Responses inline...

>If John Smith is in the database twice with one email, you want him 1 time?
YES

>If John Smith is in the database twice with two emails you want him twice, once for each email?
YES

>If Larry Smith and John Smith (brothers that share a computer) are both in the database and have the same email, what do you want returned?
For this - I'd want both emails (same), if names are different.

In production, this is for a comment system that saves by the page the comment is on. After i approve each comment, I want an email to be sent to the previous commentors for that page. I can't just loop throgh the recordset, because of someone has a few back-and-forth comments, he or she would get multiple notifications, which is a deal-breaker for me.

so im trying to figure out how, via SQL, i can just not pull the duplicates.

If this won't work, i'll revert to pulling ALL comments details for that page, putting in to an array, and then working with the array. I really wanted to avoid this though.

 

[GhostWolf]

SELECT Distinct contact_email, contact_name, contact_id
FROM db_contact
WHERE contact_comment=TRUE and contact_form_page= '/folder/pagename.asp'

Assuming the WHERE condition is met, should yield these results:
Email Name ID
johnsmith@tektips.com John Smith 437
johnsmith@yahoo.com John Smith 624
larryjones@yahoo.com Larry Jones 108
thepres@google.com George Bush 200

I don't think those return values are correct. Using the Contact_ID parameter, Larry Jones exists as 108 and 109 - albeit with the same e-mail address - so both will be returned by the quoted SQL.

I'd suggest omitting the Contact_ID selection, and selecting for only Distinct Contact_email and Contact_Name.

 

[randy700]

GhostWolf is correct. I should not have included the contact_id in the select clause.


Randy

 

[PHV]

I'm wondering if this is set up in the best way
I don't see the reason why you use the DISTINCT predicate in an aggregate query.
I'd use this:

msql = "SELECT contact_email, contact_name, MIN(contact_id) AS contact_id FROM db_contact WHERE contact_comment=TRUE and contact_form_page = '/folder/pagename.asp' GROUP BY contact_email, contact_name"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886