using Distinct with other fields in same query

[GreyHayes]

I need some query help, I think...

I have a mysql db with a list of doctors in it. Each doctor has a unique medid, but can have multiple entries because they have different specialties and departments.

In generating a phone list, I need to pull their distinct medid's so that they aren't in the phone list twice, but dont seem to be able to generate a query that gives me the right results.

Here's a sample..

217 450 Neuroscience Neurology Abbott Faith
37 173 Medicine Allergy Akbar Raana W.
264 2 Radiology Interventional Radiology Akbar Jamal U.
279 2 Radiology Radiology Akbar Jamal U.

fields are uniqueid/medid/dept/specialty/lastname/firstname/middle

I have used "SELECT distinct medid .... " and that works, but as soon as the other fields are added, I get duplicate records.

Jamal Akbar, medid=2, should only have 1 record pulled.

Can anyone help?

[coopster]

Welcome to WebmasterWorld, GreyHayes!

Yes, you will get duplicates if you tell it you want DISTINCT rows and include the non-repeating column information, such as uniqueid, specialty, etc.

If you just want a unique listing, you can only include those columns that will always be the same for each person:

SELECT DISTINCT medid, lastname, firstname, middle FROM doctors ORDER BY lastname, firstname, middle;

However, that said, you may actually want to split your table into two relational tables so you don't have repetitive information in this file. For example, if a doctor specializes in 20 different fields, then his/her name would be repeated in this file 20 times. You might want to normalize the tables something like (pseudocode):

CREATE TABLE doctors (medid, lastname, firstname, middle, ... , PRIMARY KEY (medid)); 
CREATE TABLE depts (uniqueid, medid, dept, specialty, ... , PRIMARY KEY (uniqueid));

Now you would always have a unique listing of doctors in the "doctors" file, and if you ever wanted to list their departments, you would use a JOIN to "hook" the two files together.

[GreyHayes]

thanks coopster for your help...

unfortunately, the data in this table is put there from a csv export from another system so the two table idea won't be ideal for them. I agree that a joined table would be best, but not an option here (bummer!.. but isn't that how it always is? the easy options usually aren't available :)

There are actually more fields in each row (phone, fax, cell, address, city, state, degree, etc.).. and there are even doctors with the same name (potentially), so the only "truly" unique field is their medid....

so what you're saying is that i probably need to run two queries?

one with "Select distinct medid..."
and then while looping through those rows, then go back and select the other info in a seperate query?

[GreyHayes]

ahha! I got what you were saying.. their name, medid, etc. are distinct for each Dr. (and phone/fax/cell too)... and I'm certain of that because the duplicate records are created from one master on the other end of this process...

so I just did distinct and listed all the fields I needed for the phone directory and it worked.

thanks for putting up with my lack of brilliance :)

[coopster]

Good for you.

I guess to summarize it all, think of DISTINCT as a whole row of data. If anything returned in the entire row isn't the same as the previous row returned, this new row is unique and is returned.