Use result from query in same query?

Question

I have a MySQL question, I'm wondering if the answers lies in using joins. I apologize in advance if the title is confusing or misleading.

I have 2 tables: Teams, Contractors

TEAMS
|team_id | location|
--------------------
|    1   |  space1 |
--------------------
|    2   |  space2 |
--------------------
|    3   |  space3 |
--------------------

CONTRACTORS
|cont_id | location| team_id|
-----------------------------
|    1   |  space1 |    1   |
-----------------------------
|    2   |  space1 |    0   |
-----------------------------
|    3   |  space3 |    3   |
-----------------------------
|    4   |  space3 |    3   |
-----------------------------
|    5   |  space3 |    0   |
-----------------------------

I would like to create a query that finds all the contractors that are at a specific location. The issue is, I only know the team_id. (team_id = 0 in the CONTRACTORS table means the contractor is not currently part of a team).

For example, I'm given the team_id = 3. The team with team_id = 3 is located at space3. I want to find all the contractors that are located at space3 (cont_id = 3, 4, 5 in this example).

Is there a way to achieve this with a single MySQL query? Or do I need to perform a query to get the location of the team, and then a 2nd query to find all contractors at that location?

Just a quick thank you to those who took a moment to help me out. :) — brybott
– brybott, Commented Jan 28, 2014 at 6:45

Damith · Accepted Answer · 2014-01-28 06:07:45Z

0

select location 
from CONTRACTORS 
inner join TEAMS on TEAMS.location = CONTRACTORS.location and TEAMS.team_id = 3

answered Jan 28, 2014 at 6:07

Damith

2,0923 gold badges29 silver badges44 bronze badges

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

1 Comment

brybott Over a year ago

I had to select CONTRACTORS.location, but otherwise this worked perfectly, thank you.

Agha Umair Ahmed · Accepted Answer · 2014-01-28 06:23:33Z

0

You can use join

SELECT        t.*,c.* 
FROM          TEAMS 
INNER JOIN    CONTRACTORS c 
ON            c.team_id = t.team_id 
WHERE         t.team_id = 3

edited Jan 28, 2014 at 6:23

answered Jan 28, 2014 at 6:08

Agha Umair Ahmed

1,0207 silver badges12 bronze badges

Comments

ncf · Accepted Answer · 2014-01-28 06:36:38Z

0

One SQL query:

SELECT cont_id
FROM teams, contractors
WHERE teams.location = contractors.location
AND teams.team_id = 3;

Replace '3' with the team you want.

Note: this query is only returning cont_id. You didn't specify what exactly you need, but I'm guessing at the very least you need the cont_id to identify the contractors.

edited Jan 28, 2014 at 6:36

answered Jan 28, 2014 at 6:16

ncf

5564 silver badges16 bronze badges

Collectives™ on Stack Overflow

Use result from query in same query?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related