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


Problem :

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



Solution :

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)

Mysql Tutorials

Mysql Howto..

How to produce the result as a JSON string in php [closed]

How is MySQL Uptime's value “computed”?

How to select specific and distincet number of records in multiple columns?

How to retrieve items with hibernate clustered by to_days + COUNT(*)

How to select the data from MySQL table only one record if there are same multiple records

How to get the information from table to another page using PHP and MYSQL?

How long can a hyperlink be?

How do I make this select in Single query?

How to connect Crystal Report and MySQL in VB.NET

How to fetch only certain rows from an MySql database table

How to display tweets from twitter using Hash tags by PHP+MySQL

How to upload photo with text?

How to know if PDO inserted a record on a table with a composite key?

How does one build the mysql extension for PHP 5.6?

How to create a trigger with multiple actions in MySQL 5.0.45?

how to link mysql results to the same page to show more details

How to extract the week from a date in MySQL using arbitrary weekday as the start of the week?

Could you please tell me how to display comments that are not having replies using php and mysql [closed]

How to change a MySQL column name that has # (Hash) symbol

how to make a fixed-number-of-words-search dynamic in Coldfusion/MySql?

how to apply the diff.sql into the mysql database from shell script

How to turn an image's file path into the actual image?

how to invoke the neweset one week recored in mysql?

how to synchronize two databases of different servers?

how to handle very long SQL INSERT statement in mysql

How to POST Bootstrap modal form values to MYSQL database with PDO

How to write Django QuerySet to get UNIX_TIMESTAMP of a DateTimeField?

How to dynamically set an increasing value per row in MySQL

How to get one value from other categories in php mysql?

How to export a table to mysql server?