What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?

Question

The following MySQL query:

select `userID` as uID,
(select `siteID` from `users` where `userID` = uID) as `sID`,
from `actions`
where `sID` in (select `siteID` from `sites` where `foo` = "bar")
order by `timestamp` desc limit 100

…returns an error:

Unknown column 'sID' in 'IN/ALL/ANY subquery'

I don't understand what I'm doing wrong here. The sID thing is not supposed to be a column, but the 'alias' (what is this called?) I created by executing (select siteID from users where userID = uID) as sID. And it’s not even inside the IN subquery.

Any ideas?

Edit: @Roland: Thanks for your comment. I have three tables, actions, users and sites. The table actions contains a userID field, which corresponds to an entry in the users table. Every user in this table (users) has a siteID. I'm trying to select the latest actions from the actions table, and link them to the users and sites table to find out who performed those actions, and on which site. Hope that makes sense :)

The WHERE is evaluated before the SELECT list columns can be computed. So at the time of evaluating the WHERE the column sID simply does not yet exist. What are you trying to do? You probably need a simple JOIN between actions and users, but It is unclear to me what you are trying to achieve. explain that, and I can suggest a query. — Roland Bouman, Dec 30 '09 at 16:15

score 12 · Accepted Answer · answered Dec 30 '09 at 16:19

You either need to enclose it into a subquery:

SELECT  *
FROM    (
        SELECT  userID as uID, (select siteID from users where userID = actions.userID) as sID,
        FROM    actions
        ) q
WHERE   sID IN (select siteID from sites where foo = "bar")
ORDER BY
        timestamp DESC
LIMIT   100

, or, better, rewrite it as a JOIN

SELECT  a.userId, u.siteID
FROM    actions a
JOIN    users u
ON      u.userID = a.userID
WHERE   siteID IN
        (
        SELECT  siteID
        FROM    sites
        WHERE   foo = 'bar'
        )
ORDER BY
        timestamp DESC
LIMIT   100

Create the following indexes:

actions (timestamp)
users (userId)
sites (foo, siteID)

Upvote for suggesting the join. Mysql does a poor job of handling subqueries. — mozillalives, Dec 30 '09 at 16:37

score 3 · Answer 2 · answered Dec 30 '09 at 16:18

The column alias is not established until the query processor finishes the Select clause, and buiulds the first intermediate result set, so it can only be referenced in a group By, (since the group By clause operates on that intermediate result set) if you want ot use it this way, puit the alias inside the sub-query, then it will be in the resultset generated by the subquery, and therefore accessible to the outer query. To illustrate

(This is not the simplest way to do this query but it illustrates how to establish and use a column alias from a subquery)

 select a.userID as uID, z.Sid
 from actions a
 Join  (select userID, siteID as sid1 from users) Z, 
     On z.userID = a.userID
 where Z.sID in (select siteID from sites where foo = "bar") 
 order by timestamp desc limit 100

mikecsh · Answer 3 · 2009-12-30T16:28:33.043

1

Try the following:

SELECT
       a.userID as uID
       ,u.siteID as sID
    FROM
       actions as a
    INNER JOIN
       users as u ON u.userID=a.userID
    WHERE
       u.siteID IN (SELECT siteID FROM sites WHERE foo = 'bar')
    ORDER BY
       a.timestamp DESC
    LIMIT 100

edited Dec 30 '09 at 16:28

answered Dec 30 '09 at 16:16

mikecsh

852
7
12

You an have a subquery in a select list, provided the subquery is scalar. the error message refers to the fact that the WHERE references a column which simply does not exist at the time of evaluating it. – Roland Bouman Dec 30 '09 at 16:19
`@Roland Bouman`: this query is correct, I see no reason to downvote it. – Quassnoi Dec 30 '09 at 16:20
The answer does not preclude returning a scalar in a subquery in the select list - it states you cannot return a *set*. I should have referred to the error message though. – mikecsh Dec 30 '09 at 16:23
`@mikecsh`: A field called `userID` in a table called `users` tends to be a `PRIMARY KEY`, so the subquery will not be able to return more than one record. Your query is correct but the statement about subqueries and sets is confusing. I'd just remove it if I were you :) – Quassnoi Dec 30 '09 at 16:26
Perhaps I downvoted too soon. At any rate, the original answer before the edit did not identify the source of the error message, and I don;t think the original question contained enough information that the subquery would return a set. If I did wrong, I apologise. – Roland Bouman Dec 30 '09 at 17:06

Adriaan Stander · Answer 4 · 2009-12-30T16:27:36.127

Try the following

SELECT 
       a.userID as uID 
       ,u.siteID as sID 
    FROM 
       actions as a 
    INNER JOIN 
       users as u ON u.userID = a.userID 
    INNER JOIN
        sites as s ON u.siteID = s.siteID   
    WHERE 
       s.foo = 'bar'
    ORDER BY 
       a.timestamp DESC 
    LIMIT 100

If you wish to use a field from the select section later you can try a subselect

SELECT  One,
        Two,
        One + Two as Three
FROM    (
            SELECT  1 AS One,
                    2 as Two
        ) sub

score 0 · Answer 5 · answered Dec 30 '09 at 16:19

I think the reason for the error is that the alias isn't available to the WHERE instruction, which is why we have HAVING.

select `userID` as uID,
(select `siteID` from `users` where `userID` = uID) as `sID`,
from `actions`
HAVING `sID` in (select `siteID` from `sites` where `foo` = "bar")
order by `timestamp` desc limit 100

Though i also agree with the other answers that your query could be better structured.

score 0 · Answer 6 · answered Aug 26 '20 at 11:17

I don't know whether this was not in the SQL standard 11 years ago, but I found it the easiest way to use HAVING:

select `userID` as uID,
(select `siteID` from `users` where `userID` = uID) as `sID`,
from `actions`
order by `timestamp` desc limit 100
HAVING `sID` in (select `siteID` from `sites` where `foo` = "bar")

What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?

6 Answers6

Linked