CODEWITHSUNDEEP
mysql
puls8
puls8

Reputation: 15

Joining two tables by id in mysql

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

Answers (5)

Adem Öztaş
Adem Öztaş

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

Muur
Muur

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

T00rk
T00rk

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

xQbert
xQbert

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

Erik
Erik

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

Related Questions