MySQL Join Multiple (More than 2) Tables with Conditions

Question

Assume I have 4 tables:

Table 1: Task

ID     Task            Schedule
1      Cut Grass         Mon
2      Sweep Floor       Fri
3      Wash Dishes       Fri

Table 2: Assigned

ID     TaskID (FK)     PersonID (FK)
1          1                1
2          1                2
3          2                3
4          3                2

Table 3: Person

ID     Name
1      Tom
2      Dick
3      Harry

Table 4: Mobile

ID     PersonID (FK)     CountryCode     MobileNumber
1          1                 1           555-555-5555
2          2                44           555-555-1234
3          3                81           555-555-5678
4          3                81           555-555-0000

I'm trying to display the

Task on a certain day
Name of person assigned to task
Phone numbers of said person

I think it should be something like the following, but I'm not sure how to set up the conditions so that the results are limited correctly:

SELECT T.ID, T.Task, P.Name, M.MobileNumber
FROM Task AS T
LEFT JOIN Assigned AS A
     ON T.ID = A.TaskID
LEFT JOIN Person AS P
     ON A.PersonID = P.ID
LEFT JOIN Mobile AS M
     ON M.PersonID = P.ID
WHERE T.Schedule = Fri

My goal is to fetch the following information (it will be displayed differently):

Tasks                        Name             MobileNumber
Sweep Floor, Wash Dishes     Dick, Harry      44-555-555-1234, 81-555-555-5678, 81-555-555-0000

Of course, if JOIN is the wrong way to do this, please say so.

Bryan Alves · Accepted Answer · 2011-07-17 03:59:52Z

1

It's unclear what you want to do with duplicate data in this case, but you should be looking at using inner joins instead of outer joins, and using something like group_concat() to combine the phone numbers.

answered Jul 17, 2011 at 3:59

Bryan Alves

6294 silver badges7 bronze badges

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

6 Comments

user928984 Over a year ago

Why inner joins vs outer joins? If there were no one assigned to a task at the moment, wouldn't that mean that I would get no results?

ypercubeᵀᴹ Over a year ago

Then you need an outer join on the Task-Assign joining. You also probably need an outer join on the Person-Mobile join (to catch persons without mobiles). But for Assign-Person, an inner join is enough (unless Assign.PersonID field is nullable.)

user928984 Over a year ago

@ypercube Thanks, but I ended up using all outer joins because of the nullable fields. Also, if anyone is interested, I ended up using IFNULL() with the GROUP_CONCAT part. Also, Bryan Alves, thanks for the suggestion to use GROUP_CONCAT

ypercubeᵀᴹ Over a year ago

@user928984: OK. GROUP_CONCAT() is very useful for these things. You can also define the order of the concatenated values and the separator between them: dev.mysql.com/doc/refman/5.1/en/…

ypercubeᵀᴹ Over a year ago

Also check the COALESCE() function that is more general than IFNULL() and available in other RDBMS too: dev.mysql.com/doc/refman/5.0/en/…

|

Collectives™ on Stack Overflow

MySQL Join Multiple (More than 2) Tables with Conditions

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related