MySQL query joining

Question

I have a MySQL question. It is related to this other question that I asked previously, but slightly different.

I have 2 tables: Teams, Contractors

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

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

If I'm given a team_id, I would like to create a query that finds all the contractors that are at the location of the given team_id AND belong only to the given team_id or with a team_id = 0. (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 the contractors that are located at space3 and belong only to team_id = 3 or team_id = 0 (cont_id = 3, 4, 5 in this example).

Is there a way to achieve this with a single MySQL query?

for 5 the team_id is 0 and not 3, there is mistake in the query, mine works fine..!!! — Amarnath Balasubramanian
– Amarnath Balasubramanian, Commented Feb 4, 2014 at 4:39

Santosh Ghimire · Accepted Answer · 2014-01-31 08:54:56Z

2

Try this:

select cont_id, c.team_id, c.location 
from CONTRACTORS c join TEAM t
on c.team_id = t.team_id 
where c.team_id = GIVEN_TEAM_ID or c.team_id = 0

answered Jan 31, 2014 at 8:54

Santosh Ghimire

3,1558 gold badges38 silver badges63 bronze badges

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

1 Comment

brybott Over a year ago

Thank you for your help. When I tried this, it didn't give me any of the contractors that had team_id = 0.

Vignesh Kumar A · Accepted Answer · 2014-02-01 03:52:42Z

2

Try Like this

SELECT C.cont_id ,T.team_id,T.locations
FROM TEAM T
LEFT JOIN CONTRACTORS C ON T.team_id = C.team_id OR C.team_id = 0
WHERE T.team_id = 3

edited Feb 1, 2014 at 3:52

answered Jan 31, 2014 at 8:54

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

2 Comments

Kickstart Over a year ago

The ON clause needs to use OR rather than AND , but otherwise this would be my choice.

brybott Over a year ago

Thank you for your help. Using the AND, the results were way off. When using OR as Kickstart suggested, it gave me cont_id = 2,3,4,5. So it's giving me all contractors with team_id = 0. I'm looking for just the contractors of the given team, and those in the same location but without a team.

Kickstart · Accepted Answer · 2014-02-03 09:33:57Z

1

Alternative avoiding a sub query:-

SELECT C.cont_id ,T.team_id,T.location 
FROM TEAMS T
LEFT JOIN CONTRACTORS C 
ON T.location = C.location
AND (T.team_id = C.team_id OR C.team_id = 0)
WHERE T.team_id = 3

http://www.sqlfiddle.com/#!2/b9efd/1

answered Feb 3, 2014 at 9:33

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

3 Comments

Amarnath Balasubramanian Over a year ago

for 5 the team_id is 0 and not 3, there is mistake in the query, mine works fine..

Vignesh Kumar A Over a year ago

For cont_id = 5 the team_id is 0 and not 3. Your answer is not correct

Kickstart Over a year ago

It is specifying the team id in the WHERE. It is possible that the OP wants either the team id they have specified to look up or the team the unassigned contractor is already in. Depends whether the OP wants a query which returns the team when there are no matching contractors at all (which is why it is using a left join).

Amarnath Balasubramanian · Accepted Answer · 2014-02-04 05:10:36Z

0

Select cont_id,team_id,location
FROM CONTRACTORS
where team_id = 3 or
      team_id =0 and
      location In
                 (
                    SElect location
                    from TEAMS
                    where team_id=3
                  )

Working Fiddle

Output:

enter image description here

With Joins You can do this way

SELECT C.cont_id ,C.team_id,T.location 
FROM TEAMS T
LEFT JOIN CONTRACTORS C 
ON T.location = C.location
AND (T.team_id = C.team_id OR C.team_id = 0)
where T.team_id= 3

Working Fiddle

Output:

enter image description here

edited Feb 4, 2014 at 5:10

answered Jan 31, 2014 at 9:12

Amarnath Balasubramanian

9,5028 gold badges38 silver badges62 bronze badges

2 Comments

brybott Over a year ago

Thank you for your help. This worked as needed. I was not aware you could do nested queries. I'll have to read about this. Thanks again.

Amarnath Balasubramanian Over a year ago

@but see teamid is returning 3 but actually it is 0 right??

Collectives™ on Stack Overflow

MySQL query joining

4 Answers 4

1 Comment

2 Comments

3 Comments

Working Fiddle

Working Fiddle

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related