CODEWITHSUNDEEP
mysqlsql
Jamie Hutber
Jamie Hutber

Reputation: 28076

Left Outer Join with ON & AND & WHERE not working?

The query to me looks perfectly ok but I'm still getting errors sadly:

SELECT s.id, s.uid, s.sexnumber, s.rating, s.sextime, w.who 
FROM users u, sex s 
LEFT OUTER JOIN who w 
JOIN whos ws ON s.id = ws.sid AND w.id=ws.wid 
WHERE u.sessionCheck='f9cf8142a10357d4d676f91e99a3242a' AND s.uid = u.uid 
GROUP BY s.id 
ORDER BY s.sextime ASC

With the following:

Native message: 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 'WHERE
u.sessionCheck='f9cf8142a10357d4d676f91e99a3242a' AND s.uid = u.uid GROUP '
at line 1

As far as I am aware there is no problem using a WHERE with a LEFT OUTER JOIN.

Could there be another reason why this query doesn't work?

Upvotes: 0

Views: 80

Answers (2)

xdazz
xdazz

Reputation: 160833

The problem is the below line:

LEFT OUTER JOIN who w JOIN whos ws ON s.id = ws.sid AND w.id=ws.wid

You have to change to:

JOIN whos ws ON s.id = ws.sid
LEFT OUTER JOIN who w ON w.id=ws.wid

Also you mixed the join syntax, it is suggested using the new join style only.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are mixing old-style join syntax with the better join syntax. Just say "no" to commas in the from clause. I think this is what you want:

SELECT s.id, s.uid, s.sexnumber, s.rating, s.sextime, w.who 
FROM users u join
     sex s
     on s.uid = u.uid LEFT OUTER JOIN
     whos ws
     ON s.id = ws.sid LEFT OUTER JOIN
     who w 
     ON w.id=ws.wid 
WHERE u.sessionCheck = 'f9cf8142a10357d4d676f91e99a3242a'
GROUP BY s.id 
ORDER BY s.sextime ASC;

The problem with the , operator in MySQL is actually documented here, in terms of a change from an older version.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

Upvotes: 2

Related Questions