Error in query (1054): Unknown column 'TableValue' in 'where clause'

Question

Following up with SELECTing the contents of the Table, which is a result of another value, I wanna keep a condition here on the generated field. If I execute this query:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE WHEN `IndexType`=1 THEN (
        SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID`
   ) WHEN `IndexType`=2 THEN (
        SELECT `MessageContent` FROM `Messages` WHERE `IndexRowID`=`MessageID`
   ) WHEN `IndexType`=3 THEN (
        SELECT `CommentContent` FROM `Comments` WHERE `IndexRowID`=`CommentID`
   )
END `TableValue`
FROM `Index`
ORDER BY `IndexTime` DESC;

I would obviously get:

+---------+-----------+------------+---------------------+------------+---------------------+
| IndexID | IndexType | IndexRowID | IndexTime           | IndexTable | TableValue          |
+---------+-----------+------------+---------------------+------------+---------------------+
| 5       | 2         | 2          | 2015-04-10 11:45:00 | Messages   | Hello, Mr. Prave... |
+---------+-----------+------------+---------------------+------------+---------------------+
| 4       | 3         | 1          | 2015-04-10 11:30:00 | Comments   | @You, you aren't... |
+---------+-----------+------------+---------------------+------------+---------------------+
| 3       | 2         | 1          | 2015-04-10 11:25:00 | Messages   | Thanks for your ... |
+---------+-----------+------------+---------------------+------------+---------------------+
| 2       | 1         | 2          | 2015-04-10 10:55:00 | Users      | Jeff Atwood         |
+---------+-----------+------------+---------------------+------------+---------------------+
| 1       | 1         | 1          | 2015-04-10 10:50:00 | Users      | Praveen Kumar       |
+---------+-----------+------------+---------------------+------------+---------------------+

But, if I would like to filter on the values of TableValue, so if I give a query like:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE WHEN `IndexType`=1 THEN (
        SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID`
   ) WHEN `IndexType`=2 THEN (
        SELECT `MessageContent` FROM `Messages` WHERE `IndexRowID`=`MessageID`
   ) WHEN `IndexType`=3 THEN (
        SELECT `CommentContent` FROM `Comments` WHERE `IndexRowID`=`CommentID`
   )
END `TableValue`
FROM `Index`
WHERE `TableValue` LIKE '%prav%'
ORDER BY `IndexTime` DESC;

I get the following error:

Error in query (1054): Unknown column 'TableValue' in 'where clause'.

I don't wanna use the filter for every case in the sub SELECT query. Is there any other way to do by not doing the following:

Using WHERE in each Sub-SELECT statements.
Creating a View for the whole thing.

Thanks in advance.

Using column aliases in where clause is illegal in MySQL. Refer this : http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql — Saagar Elias Jacky, Apr 26 '15 at 18:59
possible duplicate of http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error — HaveNoDisplayName, Apr 26 '15 at 19:03

score 3 · Accepted Answer · answered Apr 26 '15 at 19:00

You can't use the alias name in where clause You need to either use subquery or having clause

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE WHEN `IndexType`=1 THEN (
        SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID`
   ) WHEN `IndexType`=2 THEN (
        SELECT `MessageContent` FROM `Messages` WHERE `IndexRowID`=`MessageID`
   ) WHEN `IndexType`=3 THEN (
        SELECT `CommentContent` FROM `Comments` WHERE `IndexRowID`=`CommentID`
   )
END `TableValue`
FROM `Index`
having `TableValue` LIKE '%prav%'
ORDER BY `IndexTime` DESC;

or

select * from(
    SELECT *, (
        SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
    ) AS `IndexTypeName`, 
    CASE WHEN `IndexType`=1 THEN (
        SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID`
       ) WHEN `IndexType`=2 THEN (
        SELECT `MessageContent` FROM `Messages` WHERE `IndexRowID`=`MessageID`
       ) WHEN `IndexType`=3 THEN (
        SELECT `CommentContent` FROM `Comments` WHERE `IndexRowID`=`CommentID`
       )
    END `TableValue`
    FROM `Index`
)x
where `TableValue` LIKE '%prav%'
ORDER BY `IndexTime` DESC;

Wow! Is it that simple? Thanks! – Praveen Kumar Purushothaman Apr 26 '15 at 19:03 — Praveen Kumar Purushothaman, Apr 26 '15 at 19:03

score 2 · Answer 2 · answered Apr 26 '15 at 19:01

You could use a HAVING clause to use the alias name.

Try this

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE WHEN `IndexType`=1 THEN (
        SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID`
   ) WHEN `IndexType`=2 THEN (
        SELECT `MessageContent` FROM `Messages` WHERE `IndexRowID`=`MessageID`
   ) WHEN `IndexType`=3 THEN (
        SELECT `CommentContent` FROM `Comments` WHERE `IndexRowID`=`CommentID`
   )
END `TableValue`
FROM `Index`
HAVING `TableValue` LIKE '%prav%'
ORDER BY `IndexTime` DESC;

Wow! Is it that simple? Thanks! – Praveen Kumar Purushothaman Apr 26 '15 at 19:03 — Praveen Kumar Purushothaman, Apr 26 '15 at 19:03

score 0 · Answer 3 · answered Apr 27 '15 at 03:07

0

You can use a with clause in SQL where you can apply filter on a query's output. Its called common type expressions using with in SQL. This is more efficient.

answered Apr 27 '15 at 03:07

raj barath

7
3

Hi da. Can you explain more? Can you show a query using that? – Praveen Kumar Purushothaman Apr 27 '15 at 22:21

Error in query (1054): Unknown column 'TableValue' in 'where clause'

3 Answers3