How to get multiple row values having distinct column value

[chintan85]

Hi,

I have table 'patientdata' consisting of patient data in three columns fname, lname and loc.

I want to have a query that shows which of these patients were diagnosed in more than one location.

Here is sample table:

fname lname loc
tom hanks mi
tom hanks ca
jon trevolta nj
jon trevolta nj
tom hanks mi
jim kerry md

Answer should be:

tom hanks mi
tom hanks ca


Thanks in advance

Chintan

[Rabbit]

You could use a count distinct query that groups by the first name and last name. Anything having a count greater than 1 would be the results you're looking for. If you absolutely have to have the locations as well, then you could subquery it in the from clause.

[chintan85]

Is there any way I can accomplish this in one single query

[chintan85]

And here is the thing I dont want distinct fname or lname but I want distinct loc for same names

[Rabbit]

Yes you can do it in a single query, my post lays out how to do that.

Yes, I know. That's why you do a count distinct on loc.

[chintan85]

I added one more column dob. When I use the below command... it result as

tom    hanks            mi    10    2
jon    trevolta    nj    13    2

and not

tom    hanks            mi    10    
tom     hanks           md      10
jon    trevolta    nj    13    
jon    trevolta    nj    13    

Can you suggest me how to get this.. And thanks for your help so far

SELECT *, 
COUNT(dob) AS NumOccurrences
FROM patientdata
GROUP BY dob
HAVING COUNT(dob) > 1

[Rabbit]

That query can't possibly work. It should error out seeing as how you have a last name field and first name field that is not in an aggregate function nor in the group by clause.

Assuming dob is date of birth, why are you counting date of birth? I thought you wanted to count distinct locations.

And as I said earlier, if you want to return the location as well, you would need to subquery the aggregate query. Either into the from clause so you can join by it, or into the where clause so you can filter by it.

[chintan85]

I wanted to do this is in single query which is a priority.
Also I found out that two person can have same name and so filtering through dob is much better option... So what change I should do to query as I don't want to use subquery...

Again thanks

[chintan85]

Hey I got it.. Without using subquery. As if ur using count its expensive with large # of data instead use this.

SELECT  *
FROM    patientdata mto
WHERE   EXISTS
        (
        SELECT  1
        FROM    patientdata mti
        WHERE   mti.dob = mto.dob
        LIMIT 1, 1
        )

[Rabbit]

You just used a subquery. Plus I don't see how that is going to return the results you need. That subquery is going to return exist for every record so you're just going to get everything back.

[skrypt]

can this be a solution

SELECT DISTINCT pd1.Firstname, pd1.Lastname, pd1.Diagnosis_location, Medical_record_number
FROM patientdata pd1 JOIN patientdata pd2
USING(Medical_record_number)
WHERE pd1.Firstname = pd2.Firstname
    AND pd1.Lastname = pd2.Lastname
    AND pd1.Date_of_Birth = pd2.Date_of_Birth
    AND pd1.Diagnosis_location != pd2.Diagnosis_location
ORDER BY pd1.Lastname;