1

Can someone "break down" the syntax here. Please. I need to learn this ASAP.

From my limited experience -

firstname and lastname are columns and list is a table.

count(id)>1 is used to check if there is more than one row with the same...

That's it. I don't know what this does but I need to understand it.

SELECT firstname, lastname, list.address FROM list 
INNER JOIN (SELECT address FROM list 
            GROUP BY address 
            HAVING count(id) > 1) dup 
   ON list.address = dup.address
Improve this question
2
  • dup is a name for the table expression in the sub-query; sometimes (preferably, in my experience) prefixed by AS. Commented Oct 5, 2011 at 2:14
  • Yes: dup is an identifier, not a keyword. The dot separates the table name (dup) from the column name address. This is standard SQL notation...how much SQL do you actually know? Commented Oct 5, 2011 at 2:38

1 Answer 1

Reset to default
5

This query will return a list of all names (first and last name), which contain a duplicate address. This part

SELECT address FROM list 
GROUP BY address HAVING count(id) > 1

Gets a list of all the addresses that occur more than once in the table, This is then joined back to the table itself, to return all names which have the same address. This should return a list of all the unique address which have more than 1 name associated with them, along with the names that go along with the addresses.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I need all the duplicated selected
This will return all the duplicates.
sorry i'm learning...it will return each row so if there are 5 duplicate addresses it will return those 5 rows?
seems to easy...i have to create a table...poplulate it..and run the query to test..on way

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.