i have three tables i would like to link in this one query.
The script is an attendance register, so it records an attendance mark for each meeting, per user.
The three tables used:
"team":
id | fullname | position | class | hidden
1 | Team | -- | black | 1
2 | Dan S | Team Manager | green | 0
3 | Harry P | Graphic Engineer | blue | 0
"register":
id | mid | uid | mark
1 | 1 | 2 | /
2 | 1 | 3 | I
3 | 2 | 1 | /
4 | 2 | 3 | /
"meetings":
id | maintask | starttime | endtime
1 | Organise Year Ahead | 1330007400 | 1330012800
2 | Gather Ideas | 1330612200 | 1330617600
3 | TODO | 1331217000 | 1331222400
There is a sample of the data. What i want to do is:
Select all the results from the register, group them by the user, and order them by the meeting start time. But, if there is not a mark in the register table, i want it to display "-" (can be done via php if needed) So an expected result like so:
fullname | mark | mid
Dan S | / | 1
Dan S | / | 2
Dan S | - | 3
Harry P | I | 1
Harry P | / | 2
Harry P | - | 3
My SQL Query is this at the moment:
SELECT u.
fullname
,u.id
,r.mark
,r.mid
FROMteam
u FULL JOINregister
r ON r.uid
=u.id
LEFT JOINmeetings
m ON r.mid
=m.id
GROUP BY u.id
ORDER BY m.starttime
ASC
And i get an error back from MySQL:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN
register
r ON r.uid
=u.id
LEFT JOINmeetings
m ON r.mid
=m.`id' at line 1
But, i cant see an issue with it :S
Please could someone help out, point me in the right direction or give me a possible solution to this. Much Appreciated
Dan
Answer: Query that worked:
SELECT
u.fullname, u.id as uid,
if(r.uid = u.id, r.mark, '-') as mark,
if(r.uid = u.id, r.mid, '-') as mid,
r.mid, m.starttime
FROM
team u
CROSS JOIN
register r ON u.id = r.uid
LEFT OUTER JOIN
meetings m ON r.mid = m.id
WHERE
u.hidden = 0
GROUP BY
u.id, r.mid
ORDER BY
m.starttime, u.id ASC
Full outer join
is not supported by MySQL. At least to version 5.6, you can check MySQL Join doc. A cross join
may be a workaround:
EDITED
SELECT
UxM.fullname,
r.mark,
UxM.mid,
UxM.starttime
FROM
( select u.id as uid, m.id as mid, u.fullname, m.starttime
from
team u
CROSS JOIN
meetings ) UxM
left join
register r
on UxM.uid = r.uid and UxM.mid = r.mid
ORDER BY
UxM.starttime ASC
Let me know if this solve your issue.
A simplification:
SELECT
u.fullname,
u.id AS uid,
COALESCE(r.mark, '-') AS mark,
COALESCE(r.mid, '-') AS mid,
m.id,
m.starttime
FROM
team u
CROSS JOIN
meetings m
LEFT JOIN
register r
ON r.mid = m.id
AND r.id = u.uid
WHERE
u.hidden = 0
GROUP BY
m.id, u.id
ORDER BY
m.starttime, u.id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With