I have the following tables in my database that records user errors as a log so i can see who has made errors and at what time.
log, users, errortype and timeperiod.
Users contains,
id|name
1 David
2 Mark
3 Darren
errortype contains
id | typeoferror
1 error type 1
2 error type 2
3 errortype 3
timeperiod contains
id | period
1 7am-11am
2 11am - 3pm
3 3pm - 7pm
4 7pm-11pm
and log contains
id | user | date | time | staff | typeofmiss | timeperiod | dateoferror | notes
1 | 1 |1/1/15|11:23 | 2 | 2 | 3 | 1/1/15| blah
I would like the user column and the staff column both to link to the users table and the typeofmiss column to link to the errortype column and the timeperiod column to link to the timeperiod table so that when i have run a query it would return:
1 - David - 1/1/15 - 11:23 - Mark - errortype 2 - 3pm-11pm - 1/1/15 - blah
I have managed to get a query partly working, but i can't get the link the data from the users table twice i.e. for the user column and staff column, it returns the same name for both columns. Is there a way to get this to workor do i have to get the data out and then change the numbers to the names using php or something?
Thanks
You need to alias the tables and then specify the (aliased) columns to select.
SELECT
a.id, a.user, a.date, b.name AS user_name, c.name AS staff_name, d.typeoferror
FROM
log AS a
JOIN
users AS b ON a.user = b.id
JOIN
users AS c ON a.staff = c.id
JOIN
errortype d ON a.typeofmiss = d.id