mysql with subquery on same table

Question

i have a single mysql table with at minimum userId, wager. Data can be

userId = 1 wager = 10.00, userId = 2 wager = 5.00, userId = 3 wager = 1.00.

I want to run a select with a userId param that returns all userId's that are <= the wager for that user So if userId = 2 I get back all other users with a wager of 5.00 or less. Query does a lot more than this with one join but just need some help or best approach with the above

Tim Biegeleisen · Accepted Answer · 2016-02-26 06:55:37Z

6

You can do it with a self-join:

SELECT u1.*
FROM user AS u1
INNER JOIN user AS u2 ON u1.wager <= u2.wager
WHERE u2.userId = ?

edited Feb 26, 2016 at 6:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Feb 26, 2016 at 6:52

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Comments

Lim Neo · Accepted Answer · 2016-02-26 07:16:25Z

0

SELECT userA.*
FROM user AS userA,user AS userB 
WHERE userA.wager <= userB.wager 
AND userA.userId=userB.userId
AND userB.userId = 2

It's a good question and hope it helps you. I think most people forgot to link the userId together and it may need to link the userId together.

answered Feb 26, 2016 at 7:16

Lim Neo

758 bronze badges

Collectives™ on Stack Overflow

mysql with subquery on same table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related