mySQL - subquery with multiple fields

Question

I have 2 tables.

Table Matches

id | TeamA    | TeamB
--------------
1  | Barça    | Madrid
2  | Valencia | Depotivo

Table Payments

idMatch | User  | Quantity
---------------------------
1       | Me    | 50
2       | Me    | 100

Then in one query I want to get TeamA, TeamB, User and Quantity if they have same id.

I've tried this but it fails.

SELECT TeamA, TeamB FROM Matches WHERE id IN (SELECT idMatch, TeamA, TeamB FROM Payments)

How would your desired output look, given the example above? — eggyal
– eggyal, Commented Apr 26, 2013 at 11:44

echo_Me · Accepted Answer · 2013-04-26 11:47:21Z

2

try this

 SELECT TeamA, TeamB ,User  , Quantity FROM Matches m
 inner join Payments p
 on p.idMatch = m.id
 WHERE id IN 
 (SELECT idMatch FROM Payments)

DEMO HERE

answered Apr 26, 2013 at 11:47

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

Comments

Aditya Shanker Tagirisa · Accepted Answer · 2013-04-26 11:48:25Z

2

you can use joins

select Matches.TeamA,Matches.TeamB,Payments.User,Payments.Quantity from Matches,Payments where Matches.id=Payments.idMatch

answered Apr 26, 2013 at 11:48

Aditya Shanker Tagirisa

3231 gold badge4 silver badges12 bronze badges

Comments

Franky · Accepted Answer · 2013-04-26 11:52:45Z

2

try this

SELECT payments.user,
    payments.quantity
FROM
     Payments
     INNER JOIN Matches ON (Matches.id = Payments.idMatch);

answered Apr 26, 2013 at 11:52

Franky

9102 gold badges11 silver badges29 bronze badges

Comments

Mike · Accepted Answer · 2013-04-26 11:44:09Z

1

IN() should contain a list of values like IN('red','green','blue') or IN(1,3,53). SELECT is fine, but it has to return a single field. This would work.

SELECT TeamA, TeamB FROM Matches
WHERE id IN (
   SELECT idMatch FROM Payments
)

However it looks like you want to achieve something you need JOIN or GROUP BY for.

answered Apr 26, 2013 at 11:44

Mike

1,1765 gold badges22 silver badges33 bronze badges

Comments

Akash · Accepted Answer · 2013-04-26 11:47:06Z

1

Try

SELECT 
    TeamA, 
    TeamB,
    payments.user,
    payments.quantity
FROM 
    matches
    JOIN payments ON ( matches.id = payments.matchid )

answered Apr 26, 2013 at 11:47

Akash

5,02212 gold badges44 silver badges71 bronze badges

Comments

Fathah Rehman P · Accepted Answer · 2013-04-26 11:58:44Z

0

select m.TeamA,m.TeamB,p.`User`,p.Quantity from matches m INNER join
payments p where p.idMatch=m.id

answered Apr 26, 2013 at 11:58

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

Collectives™ on Stack Overflow

mySQL - subquery with multiple fields

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related