CODEWITHSUNDEEP
sqloracle-database
gjvatsalya
gjvatsalya

Reputation: 1169

How do I get the intersection of columns in the same table given certain criteria?

Suppose we have the following table:

E1
+-------+------------+-----------+---------+
|  ID   | FIRST_NAME | LAST_NAME | SOME_ID |
+-------+------------+-----------+---------+
| GUID1 | John       | Smith     | O1      |
| GUID2 | John       | Smith     | O2      |
| GUID3 | Andrew     | Smith     | O3      |
| GUID4 | Sam        | Jackson   | O1      |
| GUID5 | John       | Jones     | O2      |
| GUID6 | Sam        | Jackson   | O2      |
+-------+------------+-----------+---------+

If given a list of "SOME_ID" values, I need to get the intersecting values for "FIRST_NAME" and "LAST_NAME".

So for example, if "SOME_ID" is 01 and 02. Then the result will be the following:

Result
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| John       | Smith     |
| Sam        | Jackson   |
+------------+-----------+

This is due to the fact that both 01 and 02 correspond to rows which have a "FIRST_NAME" of John and "LAST_NAME" of Smith. The same logic applies for the Sam Jackson row.

So far, I have come up with the following query: 

(select first_name, last_name
from E1
where some_id = '01')
intersect
(select first_name, last_name
from E1
where some_id = '02');

This does give me the result of exactly what I want, however, it seems that there'll be a new query for each new value of "SOME_ID". This seems quite inefficient?

Is there a better way to do this? Perhaps through joins?

The other idea I had was to just get a union of all the values, and programmatically get the intersection in memory. I could be wrong, but this seems that this method would be faster if there is no better way than the way I had mentioned above.

Any help would be appreciated!

Upvotes: 0

Views: 44

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can do this with group by and having.

select first_name, last_name
from E1
where some_id in ('01','02')
group by first_name, last_name
having count(distinct some_id) = 2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Here is one method that uses group by rather than join:

select first_name, last_name
from e1
where some_id in ('01', '02')
group by first_name, last_name
having count(distinct some_id) = 2;

The performance should be pretty stable as you add more ids (change the "2" to the number of ids).

Upvotes: 1

Related Questions