Nikolay Pavlov

A friend of mine asked this question:

I have two tables: table T1 with primary key field ID, and table T2 with field ID which is a foreign key to ID from T1, and field VAL.  How do I select all records from T1, for which there are records from T2 with values X and Y? 

For example, considering the following data, I want all records from T1, where exist records records from T2 with values 1 and 2.

T1 has records
1
2
3

T2 has records

1

1

1

2

2

1

3

1

3

2

Desired output is records 1 and 3 from table 1.

The question is actually interesing only if we want to select records from table T1 for which there are an arbitrary number of records with respective values in T2.

There are a few ways to implement this.  Here is my choice number one for an arbitrary number of values:

If we are considering a few number of values - like four or five, I would suggest an alternative, which might prove even more efficient:

Providing we have a foreign key on T2.ID to T1.ID, the JOIN-approach can be really efficient. 

This entry was posted on Tuesday, February 3rd, 2009 at 12:11 pm and is filed under Database & SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.