Multiple row SQL Where clause

Question

This is probably a simple SQL statement, but it's been a while since I've done SQL and I'm having issues with it. I have this table design:

 ID   PositionId    Qty     LeagueId
 1        1          1         5
 2        3          2         5
 3        8          5         2
 4        1          6         4

What I need to get are all the rows that have specific PositionId's and Qty's. Something like:

 SELECT       ID, PositionId, LeagueId, Qty
 FROM         Lineups
 WHERE        (PositionId = 1 AND Qty = 1) AND (PositionId = 3 AND Qty = 2)

What I'm trying to get is LeagueId 5 returned since it has both PositionId of 1 and Qty 1 and PositionId of 3 and Qty 2. I don't want to use an OR statement because if I change the WHERE to:

 WHERE (PositionId = 1 AND Qty = 1) OR (PositionId = 3 AND Qty = 1)

Then LeagueId of 5 will still get returned.

Answer 1

Try this:

   Select Distinct LeagueId
   From LineUps L
   Where Exists (Select * From LineUps
                 Where LeagueId = L.LeagueId
                    And PositionId = 1 
                    And Qty = 1)
     And Exists (Select * From LineUps
                 Where LeagueId = L.LeagueId
                    And PositionId = 3 
                    And Qty = 2)

This more closely semantically represents your intent

Answer 2

A general way of performing this would be:

 SELECT       LeagueId
 FROM         Lineups
 WHERE        (PositionId = 1 AND Qty = 1) OR (PositionId = 3 AND Qty = 2) OR ...
 GROUP BY     LeagueId
 HAVING COUNT(*) = <number of OR'ed together clauses>