Geek Answers Handbook

What happened to this MySQL query? SELECT * AS `x`, how to use x again later?

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 do not understand what I'm doing wrong here. Thing sID should not be a column, but the “alias” (what is called?) That I created by doing (select siteID from users where userID = uID) as sID . And this is not even inside the IN subquery.

Any ideas?

Edit: @Roland: Thanks for your comment. I have three tables, actions , users and sites . The actions table contains the userID field that corresponds to the entry in the users table. Each user in this table ( users ) has a siteID . I am trying to select the last actions from the actions table and associate them with the users and sites tables to find out who performed these actions and on which site. Hope this makes sense :)

+4
source share
5 answers

You need to either enclose it in 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 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)
+11
source

The column alias is not set until the query processor finishes the Select clause and creates the first intermediate result set, so it can only be referenced by the By group (since the group By clause works on this intermediate result set) if you want to use it like this thus, try an alias inside the subquery, then it will be in the result set generated by the subquery, and therefore available for an external query. To illustrate

(This is not the easiest way to make this query, but it illustrates how to set 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
+3
source

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
+1
source

Try to execute

 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 want to use the field from the selection section later, you can try the subquery

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

I think the reason for the error is that the alias is not available for the WHERE statement, so we have.

 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

Although I also agree with the other answers, your request may be better structured.

0
source

More articles:


All Articles