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

This should return 5:

SELECT DISTINCT lineups1.leagueid
FROM lineups AS lineups1 INNER JOIN lineups AS LINEUPS2 
ON lineups1.LeagueId=lineups2.LeagueId
WHERE lineups1.PositionId=1 AND lineups2.Qty = 1 
  AND  lineups2.PositionId=3 AND lineups2.Qty = 2

Since you can only select single rows, you have to JOIN another table if you want to consider more than one. In this case, the table you're "self-joining" lineups, retrieving the value from one row based on conditions from another row (of course it doesn't matter whose leagueid you take because they're identical).

Update You can of course extend this to

SELECT lineups1.ID, ..., lineupts2.ID, ...

to retrieve whichever fields you want to retrieve.

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>

Answer 3

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 4

You can also try:

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