SQL query using no subqueries

A while back I came onto this site to try and find a query, and ended up with this below. What it does, and what I need it to do, is to look into the table 'data' and return the first name, last name, and date of birth of any customer who has been treated at two different diagnosis_locations. It works.

  SELECT firstname, lastname, date_of_birth
    FROM `data`
   WHERE diagnosis_location IN (SELECT DISTINCT Diagnosis_location 
                                  FROM `data`)
GROUP BY firstname, lastname, date_of_birth
  HAVING COUNT(DISTINCT diagnosis_location) >= 2

However, now I need to 开发者_JS百科take this same query and get an identical result without using any subqueries. Could anyone help me here? Or at least give me some pointers?

From what I see your query returns records where diagnosis_location field is not equal null, so you can rewrite it as WHERE diagnosis_location IS NOT NULL

The way you have it is fine. The only other way to do it would be to use temp tables. Unless your table is huge the added complication is not worth it

EDIT: im assuming you have two columns, Diagnosis_location and diagnosis_location. If that isnt true and you just made a typo then this answer is wrong.

If you are looking to write without a subquery, try a join statement:

SELECT firstname, lastname, date_of_birth          
FROM `data` A          
JOIN `data` B
ON A.diagnosis_location = B.Diagnosis_location 
GROUP BY firstname, lastname, date_of_birth 
HAVING COUNT(DISTINCT diagnosis_location) >= 2

Out of curiousity, if you looking for a perfomance increase and the join doesn't work well, you should be able to try an EXISTS statement instead of the IN statement:

SELECT firstname, lastname, date_of_birth 
FROM `data` A
WHERE EXISTS  
(SELECT 1 
FROM `data` B 
where B.Diagnosis_location = A.diagnosis_location) 
GROUP BY firstname, lastname, date_of_birth 
HAVING COUNT(DISTINCT diagnosis_location) >= 2