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