CODEWITHSUNDEEP
mysqlsqldatabase
hawx
hawx

Reputation: 1669

Left Join - Unknown Column Error 1054

I keep getting #1054 - Unknown column 'colour' in 'where clause'.

A simplified version of my query is as follows.

SELECT *, "red" AS colour
FROM (
    SELECT *,'calls' AS 'tbl', id AS cid FROM calls) as c
WHERE colour='red' 
ORDER BY colour ASC, c.created_date DESC

If I remove the WHERE line the query executed fine and colour is recognised and all is well.

Any help would be appreciated.

Upvotes: 0

Views: 256

Answers (4)

Slowcoder
Slowcoder

Reputation: 2120

Usage of alias for a column in where clause is not a good approach. Please refer the following link.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name  WHERE cnt > 0 GROUP BY id;

Use HAVING for alias names or change the condition to use the exact column name in WHERE clause.

Upvotes: 0

Marc
Marc

Reputation: 16512

It's because you can't use an alias in your WHERE and ORDER BY clause

So you would have to do something like

SELECT *, IF(c.status = '1', "green", "red") AS colour
FROM (
    SELECT *,'calls1' AS 'tbl', id AS cid FROM calls1  
    WHERE uid LIKE '%1111%' 
    ORDER BY timestamp DESC
    UNION
    SELECT *,'calls2' AS 'tbl', id AS cid FROM calls2  
    WHERE uid LIKE '%1111%' 
    ORDER BY timestamp DESC
) as c
LEFT JOIN objects AS o ON o.call_id = c.id 
WHERE c.status = 'the color id you want' // or you can rewrite your if here
ORDER BY c.status ASC, c.timestamp DESC

Or this for your simplified query

SELECT *, "red" AS colour
FROM (
    SELECT *,'calls' AS 'tbl', id AS cid FROM calls) as c
WHERE c.status = 'the color id you want' 
ORDER BY c.created_date DESC

Why sorting by color if it's always red?

Upvotes: 0

rs.
rs.

Reputation: 27427

For your updated question i think you can do

SELECT * FROM (
SELECT *, "red" AS colour
FROM (
    SELECT *,'calls' AS 'tbl', id AS cid FROM calls) as c
) as v
WHERE colour='red' 
ORDER BY colour ASC, v.created_date DESC

Upvotes: 2

jcho360
jcho360

Reputation: 3759

try this WHERE c.status !='1'

SELECT *, IF(c.status = '1', "green", "red") AS colour
FROM (
    SELECT *,'calls1' AS 'tbl', id AS cid FROM calls1  
    WHERE uid LIKE '%1111%' 
    ORDER BY timestamp DESC
    UNION
    SELECT *,'calls2' AS 'tbl', id AS cid FROM calls2  
    WHERE uid LIKE '%1111%' 
    ORDER BY timestamp DESC
) as c
LEFT JOIN objects AS o ON o.call_id = c.id 
WHERE c.status != '1'
ORDER BY colour ASC, c.timestamp DESC

Upvotes: 0

Related Questions