CODEWITHSUNDEEP
mysql
Morts81
Morts81

Reputation: 439

MySQL Query of Column Based on Alternative Column

I’ve trawled through a number of conditional MySQL type questions online but haven’t come across anything that helps with this query, it’s likely I’m describing the question incorrectly in my internet search, however, if anyone can help it would be greatly appreciated.

Assume I have a table titled results with the three columns outlined below how do I query the table for all teammates of James? I know I can query WHERE Team = 'B', however, if I don’t know the team name, how do I get all teammates of James?

Team     Name   Place
A        John   1
B        James  2
B        Harry  3
C        Brad   4

What I want to return is the following:

Team     Name   Place
B        James  2
B        Harry  3

Is a two query type approach required, where I first query the team name for James and then use the output of that query to undertake a second query?

Upvotes: 0

Views: 34

Answers (2)

flyingfox
flyingfox

Reputation: 13506

SELECT t1.* FROM results t1 where t1.Team IN
  (SELECT t2.Team FROM results t2 WHERE t2.Name='James')

OR

SELECT t1.* FROM results t1
   JOIN results t2 on t1.team=t2.team
where t2.name='James'

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You can use in operator 

SELECT * FROM results as t1 
where  t1.team in (SELECT team FROM results  WHERE name='James')

Upvotes: 0

Related Questions