SQL to select half of a two-part composite key based on multiple rows of keys?

Question

I have a table with a composite key like this:

========TABLE========
key_a_col | key_b_col

Scenario:

key_b is 'foo', and there is a 'bar' and 'baz' entry under key_a for it. Another key_b 'fiz' has 'bar' and 'raz'.

Like this:

========TABLE========
key_a_col | key_b_col
    'bar' | 'foo'
    'baz' | 'foo'
    'bar' | 'fiz'
    'raz' | 'fiz'

I want to select all key_b entries where there is both a 'bar' and a 'baz' paired with it in key_a_col. So 'foo' would be returned (bar|foo and baz|foo exist), while 'fiz' would not (only fiz|bar matches).

How can I make this happen?

See this question: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) — ypercubeᵀᴹ, Apr 20 '12 at 00:37

score 0 · Answer 1 · answered Apr 20 '12 at 00:56

The link that ypercube should give you an idea, but if you want to go further than that to N conditions, you'll have to do N joins.

Heres another approach that you can use to do arbitrary conditions sans joins. Create a table Conditions with your 'biz' and 'baz'

SELECT DISTINCT col_b FROM Table t1 
       WHERE ( SELECT COUNT(*) FROM Table t2 
                INNER JOIN Condition c on t2.key_b = c.condition
                WHERE t1.key_a = t2.key_a 
                GROUP BY t1.key_a) =  
              (SELECT COUNT(*) FROM CONDITION)

score 0 · Accepted Answer · edited May 23 '17 at 11:52

ypercube's link was perfect. I learned more than a little bit. Anyone reading this in the future should go to:

How to filter SQL results in a has-many-through relation

I settled on:

SELECT distinct(t.b_id)
FROM   columns_table t
WHERE  EXISTS (SELECT * FROM columns_table 
               WHERE  columns_table.id = t.id AND columns_table.col_a = 'bar')
AND 
EXISTS (SELECT * FROM columns_table 
               WHERE  columns_table.id = t.id AND columns_table.col_a = 'baz')

It looks like I can chain WHERE EXISTS clauses an arbitrary number of time and still get good performance. I can also join other tables without making the query complicated to read. If I could upvote ypercube's comment I would.

Whoops. Done. Thanks again for the link. – jeremiahs Apr 20 '12 at 07:31 — jeremiahs, Apr 20 '12 at 07:31

SQL to select half of a two-part composite key based on multiple rows of keys?

2 Answers2