MySQL Query of Column Based on Alternative Column

Question

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?

Why not sit down with any basic book or tutorial? It will be of fantastic benefit. — Strawberry
– Strawberry, Commented Jul 24, 2018 at 5:36
Couldn’t agree more on the tutorials and I tried to find something with worked examples but I was obviously classifying the question incorrectly. Rest assured, my first port of call is to always try work these things out for myself first. — Morts81
– Morts81, Commented Jul 24, 2018 at 5:41
Totally agree. Imagine how much time I wasted on this though. — Morts81
– Morts81, Commented Jul 24, 2018 at 5:46

flyingfox · Accepted Answer · 2018-07-24 05:24:32Z

1

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'

edited Jul 24, 2018 at 5:24

answered Jul 24, 2018 at 5:18

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

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

2 Comments

Strawberry Over a year ago

Let's start with the second suggestion

Morts81 Over a year ago

Thankyou very much for this, exactly what I was looking for.

Zaynul Abadin Tuhin · Accepted Answer · 2018-07-24 05:19:53Z

0

You can use in operator

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

answered Jul 24, 2018 at 5:19

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQL Query of Column Based on Alternative Column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related