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 :)

Answer 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

Answer 2

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 

Answer 3

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)

Answer 4

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

Answer 5

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.