SQL join clause to return rows with more than one occurrence without grouping

Question

I want to extract the records which appear more than once from a JOIN query, but without them getting grouped.

Example tables:

tbl_names
id    Name
1     Mark
2     John
3     Jane
4     Paul

tbl_locations
id    Location
1     Berlin
2     Frankfurt
2     Stockholm
3     Helsinki
3     Madrid
3     London
4     Paris

ID is a foreign key.

Now, the result of the query would be:

id     Name    Location
2      John    Frankfurt
2      John    Stockholm
3      Jane    Helsinki
3      Jane    Madrid
3      Jane    London

i.e. all JOIN records in which the record in the first table appears more than once in the JOIN clause result.

I can get it grouped of course:

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location FROM tbl_names
 INNER JOIN tbl_locations ON (tbl_names.id = tbl_locations.id)
 GROUP BY tbl_names.id
 HAVING COUNT(tbl_names.id) > 1

What I want is to have them not grouped at all. I have tried with a sub-clause and NOT IN but it is extremely slow and doesn't give me the results I want.

Any enlightenment will be welcome.

Answer 1

Use this one:

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location
FROM tbl_names
INNER JOIN tbl_locations ON (tbl_names.id = tbl_locations.id)
where tbl_names.id in (select id from tbl_locations
                       group by id having count(*) > 1);

This select shows the join you already have, but selects only the names/ids, which have more than 1 entry in the locations table.

According to https://stackoverflow.com/a/3520552/1741542, this might be faster than a plain subquery:

create view view_id_locations as
    select id
    from tbl_locations
    group by id
    having count(*) > 1;

SELECT tbl_names.id, tbl_names.Name, tbl_locations.Location
FROM tbl_names
INNER JOIN tbl_locations ON tbl_names.id = tbl_locations.id
inner join view_id_locations on tbl_names.id = view_id_locations.id;