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.

You want to return only a single LeagueID even if the PositionID and Qty values match more than once? — user330315
– user330315, Commented Aug 15, 2010 at 17:40
Well, it would, obviously, be multiple rows, of the same LeagueId, so I'll need to use the Distinct keyword. This is just a subset of data, but it should also return all the LeagueId's that have have PositionId of 1 and Qty of 1 and PositionId of 3 and Qty of 2. — Kris B
– Kris B, Commented Aug 16, 2010 at 0:31

Will A · Accepted Answer · 2010-08-15 17:18:25Z

9

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>

answered Aug 15, 2010 at 17:18

Will A

25k5 gold badges54 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Bretana · Accepted Answer · 2010-08-15 17:19:39Z

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

answered Aug 15, 2010 at 17:19

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

2 Comments

user330315 Over a year ago

This can be written a lot easier (and probably more efficiently) using: SELECT DISTINCT LeagueId FROM LineUps WHERE (positionId, Qty) IN ( (1,1), (3,2) )

Charles Bretana Over a year ago

Never count on what effect the syntax you use will have on performance. In most DB vendor's products, the Query Processor / optimizer has wide latitude in how it decides to implement your sql, as long as it conforms to the logical intent. For that reason, unless there is a known performance issue, I would tend to use the SQL that most closely represents the logical intent.

Nicolas78 · Accepted Answer · 2010-08-15 17:20:55Z

2

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.

edited Aug 15, 2010 at 17:20

answered Aug 15, 2010 at 17:14

Nicolas78

5,1441 gold badge26 silver badges43 bronze badges

1 Comment

Nicolas78 Over a year ago

having written this, I like both Charles' and Will's approach better ;)

MeanGreen · Accepted Answer · 2015-11-11 07:56:48Z

1

SELECT       DISTINCT LeagueId /*to display non-repeating record*/
FROM         Lineups
WHERE        PositionId in (1,3) AND Qty in (1,2) /*OR*/

The First statement will return 2 records which the League ID's are 5, but if your intention is to get the league ID's containing those positions and QTY, replace the 'AND' with 'OR' then it will return league Id 4 and 5.

edited Nov 11, 2015 at 7:56

MeanGreen

3,3555 gold badges42 silver badges71 bronze badges

answered Nov 10, 2015 at 7:20

MrRobot614

396 bronze badges

2 Comments

MeanGreen Over a year ago

If you can add some explanation to this answer, it would be even better :)

MrRobot614 Over a year ago

The First statement will return 2 records which the League ID's are 5, but If your intention is to get the league ID's containing those positions and QTY replace the 'AND' with 'OR' then it will return league Id 4 and 5. Hope this will help. =)

silvo · Accepted Answer · 2010-08-15 17:20:38Z

0

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)

answered Aug 15, 2010 at 17:20

silvo

4,00924 silver badges25 bronze badges

Collectives™ on Stack Overflow

Multiple row SQL Where clause

5 Answers 5

Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related