Left Excluding JOIN in MySQL

Question

I have this working MySQL query (it's stripped down to show only relevant lines):

SELECT
  c.id AS id_comment
, bl.id_user AS block
FROM comments AS c
LEFT JOIN user_blocked AS bl ON 
  (c.id_user = bl.id_user AND :uid = bl.id_user_blocked) 
  OR (c.id_user = bl.id_user_blocked AND :uid = bl.id_user)
WHERE (c.id_title = :idt)

It works! But I just want to get results where block IS NULL so I add a new condition in WHERE:

SELECT
  c.id AS id_comment
, bl.id_user AS block
FROM comments AS c
LEFT JOIN user_blocked AS bl ON 
  (c.id_user = bl.id_user AND :uid = bl.id_user_blocked) 
  OR (c.id_user = bl.id_user_blocked AND :uid = bl.id_user)
WHERE (c.id_title = :idt) AND (block IS NULL)

But I get this error:

SQL ERROR: SQLSTATE[42S22]: Column not found: 1054
Unknown column 'block' in 'where clause'

Why? What am I doing wrong?

Take a look at [this question](http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) — Teneff, Dec 31 '12 at 02:18

score 2 · Accepted Answer · answered Dec 31 '12 at 02:16

block is an ALIAS, you should be using the name of the column instead

SELECT...
FROM...
WHERE (c.id_title = :idt) AND (bl.id_user IS NULL)

the reason why it is not found is because the order of operation is as follows:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

If you want block to be used instead of the column name, you need to put the entire query in a SubQuery. eg

SELECT  * 
FROM
(
    SELECT  c.id AS id_comment, 
            bl.id_user AS block
    FROM    comments AS c
            LEFT JOIN user_blocked AS bl 
                ON  (c.id_user = bl.id_user AND :uid = bl.id_user_blocked) OR 
                (c.id_user = bl.id_user_blocked AND :uid = bl.id_user)
    WHERE   c.id_title = :idt
) s
WHERE block IS NULL

but I prefer the first solution than using a subquery.

score 2 · Answer 2 · answered Dec 31 '12 at 02:16

2

You cannot reference the column alias; reference the true column instead:

WHERE (c.id_title = :idt) AND (bl.id_user IS NULL)

answered Dec 31 '12 at 02:16

BellevueBob

9,498
5
29
56

1

@Peter Careful (;>). And please accept JW's answer; it's more complete and beat me to it. – BellevueBob Dec 31 '12 at 02:25
1

Merry Christmas and Happy New Year Bob! `+1` – John Woo Dec 31 '12 at 02:26
Happy new year to both of you! Thanks again :) – Peter Dec 31 '12 at 02:29

Left Excluding JOIN in MySQL

2 Answers2