CODEWITHSUNDEEP
mysqlsqljoin
Peter
Peter

Reputation: 5156

Left Excluding JOIN in MySQL

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?

Upvotes: 0

Views: 1739

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

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

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

Upvotes: 2

John Woo
John Woo

Reputation: 263683

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.

Upvotes: 2

Related Questions