CODEWITHSUNDEEP
mysqlsqlselect
Mugur Ungureanu
Mugur Ungureanu

Reputation: 161

Join multiple tables

I have 3 tables. First one is a list of personnel:

Table Personnel

Id_personnel         Name
1                    John
2                    Alice
3                    Tom
4                    Charles
5                    Ben

Table Department

Id_personnel         Department
1                    Department 1
2                    Department 2
3                    Department 3
4                    Department 4
5                    Department 5

and table with medical leave:

Table Medical

Id_personnel         Start_date         End_date
1                    2012-08-02         2012-08-05
2                    2012-08-02         2012-08-15
3                    2012-10-04         2012-10-06
4                    2012-10-04         2012-10-06
5                    2012-09-20         2012-09-21

For a given date (let's say 2012-10-05) I want to have something like

Department 1                    John
Department 2                    Alice
Department 3                    Tom             2012-10-04         2012-10-06
Department 4                    Charles         2012-10-04         2012-10-06
Department 5                    Ben

Using INNER JOIN I am able to get only those sick. I have something like this:

    SELECT a.Name, b.Department, c.Start_date, c.End_date FROM Personnel a
      INNER JOIN Department b ON a.Id_personnel = b.Id_personnel
      INNER JOIN Medical c ON a.Id_personnel = c.Id_personnel
   WHERE c.Start_date <= 2012-10-05  AND c.End_date >= 2012-10-05

And the obvious wrong result is:

Department 3                    Tom             2012-10-04         2012-10-06
Department 4                    Charles         2012-10-04         2012-10-06

Upvotes: 2

Views: 113

Answers (2)

Himanshu
Himanshu

Reputation: 32602

You should give conditions in CASE Statement instead of WHERE clause like this:

SELECT a.Name, b.Department,
  CASE WHEN c.Start_date <= '2012-10-05' AND c.End_date >= '2012-10-05' 
       THEN c.Start_date ELSE '' END AS Start_date
 ,CASE WHEN c.Start_date <= '2012-10-05' AND c.End_date >= '2012-10-05'
       THEN c.End_date ELSE '' END AS END_date
  FROM Personnel a
  JOIN Department b ON a.Id_personnel = b.Id_personnel
  JOIN Medical c ON a.Id_personnel = c.Id_personnel

See this SQLFiddle

UPDATE (from comments)

If you don't want to repeat the same person for same department you need to group them like this:

GROUP BY a.Id_personnel,Department

See this SQLFiddle

UPDATE 2

You need to use GROUP_CONCAT function when the same person has more than one leave:

SELECT DISTINCT a.Name, b.Department,
  GROUP_CONCAT(CASE WHEN c.Start_date <= '2012-10-05' 
                     AND c.End_date >= '2012-10-05' 
               THEN c.Start_date ELSE '' END SEPARATOR ' ') AS Start_date
 ,GROUP_CONCAT(CASE WHEN c.Start_date <= '2012-10-05' 
                     AND c.End_date >= '2012-10-05'
               THEN c.End_date ELSE '' END SEPARATOR '') AS END_date
  FROM Personnel a
  JOIN Department b ON a.Id_personnel = b.Id_personnel
  JOIN Medical c ON a.Id_personnel = c.Id_personnel
 GROUP BY a.Id_personnel,Department

See this SQLFiddle

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

 SELECT a.Name, b.Department, c.Start_date, c.End_date
      FROM Personnel a
      Left JOIN Department b ON a.Id_personnel = b.Id_personnel
      Left JOIN Medical c ON a.Id_personnel = c.Id_personnel
   WHERE c.Start_date <= 2012-10-05  AND c.End_date >= 2012-10-05

Upvotes: 0

Related Questions