StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL FULL JOIN one table, and LEFT JOIN another in the same query?
text
Body
copied!<p>i have three tables i would like to link in this one query.</p> <p>The script is an attendance register, so it records an attendance mark for each meeting, per user.</p> <p>The three tables used:</p> <p>"team":</p> <pre><code>id | fullname | position | class | hidden 1 | Team | -- | black | 1 2 | Dan S | Team Manager | green | 0 3 | Harry P | Graphic Engineer | blue | 0 </code></pre> <p>"register":</p> <pre><code>id | mid | uid | mark 1 | 1 | 2 | / 2 | 1 | 3 | I 3 | 2 | 1 | / 4 | 2 | 3 | / </code></pre> <p>"meetings":</p> <pre><code>id | maintask | starttime | endtime 1 | Organise Year Ahead | 1330007400 | 1330012800 2 | Gather Ideas | 1330612200 | 1330617600 3 | TODO | 1331217000 | 1331222400 </code></pre> <p>There is a sample of the data. What i want to do is:</p> <p>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:</p> <pre><code>fullname | mark | mid Dan S | / | 1 Dan S | / | 2 Dan S | - | 3 Harry P | I | 1 Harry P | / | 2 Harry P | - | 3 </code></pre> <p>My SQL Query is this at the moment:</p> <blockquote> <p>SELECT u.<code>fullname</code>,u.<code>id</code>,r.<code>mark</code>,r.<code>mid</code> FROM <code>team</code> u FULL JOIN <code>register</code> r ON r.<code>uid</code>=u.<code>id</code> LEFT JOIN <code>meetings</code> m ON r.<code>mid</code>=m.<code>id</code> GROUP BY u.<code>id</code> ORDER BY m.<code>starttime</code> ASC</p> </blockquote> <p>And i get an error back from MySQL:</p> <blockquote> <p>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 <code>register</code> r ON r.<code>uid</code>=u.<code>id</code> LEFT JOIN <code>meetings</code> m ON r.<code>mid</code>=m.`id' at line 1</p> </blockquote> <p>But, i cant see an issue with it :S</p> <p>Please could someone help out, point me in the right direction or give me a possible solution to this. Much Appreciated</p> <p>Dan</p> <p>Answer: Query that worked:</p> <pre><code> 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload