Skip to content
Advertisement

Find such values from a column which must be associated with all desired values from another column

I have a lookup table with two foreign key columns. The values can be as:

A_ID B_ID
5 1
5 2
6 1
7 2
9 5
9 1
9 2

Now, How do I query this table to get all such ids of column A_ID which must be associated with a collection of desired ids of column B_ID i.e. From the table, If I pass the value {1,2}, I need to get {5, 9} from column A_ID.

Advertisement

Answer

  1. Assuming there are no duplicate entries and null values in the database.

  2. Here you are providing the list which is [1,2], so in advance, you can calculate the length of the list, here it is 2.

  3. Now let’s say have a list list1 and length of it is n then the query will look something like this

    SELECT temp.A_ID
    from (SELECT * FROM table where B_ID in list1) as temp 
    group by temp.A_ID 
    having count(*) >= n;
2 People found this is helpful
Advertisement