This query returns entries with 4 columns: id , alias , name , status .

  SELECT DISTINCT t.id, t.alias, t.name, CASE WHEN at.tag_id IS NULL THEN 0 ELSE 1 END AS status FROM tbl_tag t LEFT JOIN tbl_article_tags at ON t.id = at.tag_id

How to add a condition WHERE status = 1 ? If you add it, then mysql gives an error:

# 1054 - Unknown column 'status' in 'where clause'

    1 answer 1

    SQL Alias - can be used to set tables or columns of other names using an alias. Using aliases can be useful if you have long or complex table or column names.

    When you write AS status - you specify a name for a column, alas, it cannot be used in the WHERE . Therefore, the solution of the problem will be as follows:

      SELECT DISTINCT t.id, t.alias, t.name, CASE WHEN at.tag_id IS NULL THEN 0 ELSE 1 END AS status FROM tbl_tag t LEFT JOIN tbl_article_tags at ON t.id = at.tag_id WHERE at.tag_id IS NOT NULL
    • 3
      can still be sub-requested - Vartlok
    • 3
      @Vartlok, yes, either by sub-request, but it will be more expensive. - Denis Bubnov