Using Distinct

[TreacherousDog]

Hey hey,

Ok here is my dilemna. I have a newsletter database that is contains email addresses and the newsletter they are associated to. I want to be able to give my users the option of sending to either a specific targeted group or the list as a whole. The problem is the same email address could be in the database 3 or maybe even 4 times if they are subcribed to 4 or 5 different newsletters. I need to grab their name and their email address without duplicate email addresses. I thought SELECT DISTINCT would solve this problem but it doesn't seem to be making a difference.

$sql = "SELECT DISTINCT name, email FROM subscribers";

To better illustrate if I use the search tool I made to search for my name I get the following SQL:

SELECT DISTINCT profile.id, profile.name, profile.email, profile.company, type.newsletter FROM email_newsletter profile, email_types type WHERE profile.unsubscribed=0 AND profile.industry=type.reference AND profile.name LIKE '%Jim%Jeffers%'

But I get these results:
1588
Jim Jeffers
[e-mail]
JLT Mobile Computers
Generic

1581
Jim Jeffers
[e-mail]
JLT Mobile Computers
Government

1602
Jim Jeffers
[e-mail]
JLT Mobile Computers
Public Safety

[LordShryku]

First, in that SQL, how are you joining the tables? You never specify....

Next, distinct works on the first selection. So you are getting distinct ID's. Look at it in this syntax

SELECT DISTINCT profile.id, 
       profile.name, 
       profile.email, 
       profile.company, 
       type.newsletter 
  FROM email_newsletter profile, email_types type 
 WHERE profile.unsubscribed=0 
   AND profile.industry=type.reference 
   AND profile.name LIKE '%Jim%Jeffers%' 

That's what it will look for. So you may want to flip your distinct to choose maybe distinct email?

SELECT DISTINCT profile.email, 
       profile.id, 
       profile.name, 
       profile.company, 
       type.newsletter 
  FROM email_newsletter profile, email_types type 
 WHERE profile.? = type.?
   AND profile.unsubscribed=0 
   AND profile.industry=type.reference 
   AND profile.name LIKE '%Jim%Jeffers%' 

[ahundiak]

You should perhaps be aware that DISTINCT works on ALL the fields returned i.e. it tosses out duplicate rows.

So you can do a
SELECT DISTINCT email FROM subscribers
and get a list of distinct emails. If you need more fields then a different approach will be required.

A manual is a terrible thing to waste.