Reputation: 15
I have two tables like this:
Table_users
id | firstname | lastname
1 | John | Doe
2 | Eve | Adam
table_shifts
date | dayshift | nightshift
2014-09-17 | 1 | 2
2014-09-18 | 2 | 1
And I want this as a result:
table_shifts_overview
date | dayshift | nightshift
2014-09-17 | John Doe | Eve Adam
Where date = '2014-09-17'
I tried this with with JOIN and a subquery, but it didn't work out. Any halp would be appreciated.
Upvotes: 0
Views: 120
Reputation: 21516
You can try;
SELECT ts.date,
contact(tu.firstname ,' ', tu.lastname) AS dayshift,
contact(tu2.firstname ,' ', tu2.lastname) AS nightshift
FROM table_shifts ts, Table_users tu, Table_users tu2
WHERE ts.dayshift = tu.id AND
ts.nighshift = tu2.id AND
ts.date = '2014-09-17'
Upvotes: 0
Reputation: 345
You can use implicit join:
SELECT s.date,
CONCAT(u_day.firstname, ' ', u_day.lastname) AS dayshift,
CONCAT(u_night.firstname, ' ', u_night.lastname) AS nightshift
FROM shifts s, users u_day, users u_night
WHERE s.dayshift = u_day.id AND s.nightshift = u_night.id
AND s.date = '2014-09-17'
Or explicit join:
SELECT s.date,
CONCAT(u_day.firstname, ' ', u_day.lastname) AS dayshift,
CONCAT(u_night.firstname, ' ', u_night.lastname) AS nightshift
FROM shifts s
INNER JOIN users u_day ON (s.dayshift = u_day.id)
INNER JOIN users u_night ON (s.nightshift = u_night.id)
WHERE s.date = '2014-09-17'
Note that the keyword INNER is optional in the last query.
Here is the MySQL documentation about JOIN syntax: http://dev.mysql.com/doc/refman/5.6/en/join.html
Upvotes: 0
Reputation: 2297
Try this query
SELECT TS.date, CONCAT(TU1.firstname,' ', TU1.lastname) AS dayshift, CONCAT(TU2.firstname, ' ', TU2.lastname) AS nightshift
FROM table_shifts TS
INNER JOIN table_users TU1 on TU1.ID= TS.dayShift
INNER JOIN table_users TU2 on TU2.id= TS.NightShift
WHERE TS.Date = '2014-09-17'
Upvotes: 1
Reputation: 35353
SELECT S.date,
concat(U1.FirstName,' ', U1.LastName) dayshift,
concat(U2.FirstName,' ', U2.LastName) nightshift
FROM table_Shifts S
INNER JOIN table_users U1
on U1.ID= S.dayShift
INNER JOIn table_users U2
on U2.id= S.NightShift
WHERE S.Date = '2014-09-17'
Upvotes: 0
Reputation: 1057
You can join the same table multiple times by aliasing it:
SELECT date, u1.firstname, u1.lastname, u2.firstname, u2.lastname
FROM table_shifts
INNER JOIN table_users AS u1 ON u1.id = dayshift.id
INNER JOIN table_users AS u2 ON u2.id = nightshift.id
Upvotes: 0