SQL SubQuery getting particular column

Question

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here's my question:

The subquery below returns a Table with 3 columns, USERNAME, COMPANY, POSITION. I just want to get the USERNAME column out without altering the subquery within. I tried the following:

SELECT username from (SELECT username, company, position FROM table1 WHERE username IN (SELECT username FROM members_network WHERE xscore <= 18 AND xscore >= 15))

but the outer table is not defined. Can someone tell me how to retrieve only the username from the subquery?

Thanks All!

Hi Dan, thx for your reply. The error I am getting is: #1248 - Every derived table must have its own alias — Tilo Mitra
– Tilo Mitra, Commented Nov 13, 2009 at 5:42

Shannon Severance · Accepted Answer · 2009-11-13 05:44:39Z

2

You don't say which RDBMS you are working with. Some, such as SQL Server, require that in-line views be given a table alias. Unfortunately, I don't have time to start up a windows VM to double check, but you might try:

SELECT username 
from (SELECT username, company, position 
    FROM table1 WHERE username IN 
        (SELECT username 
        FROM members_network 
        WHERE xscore <= 18 AND xscore >= 15)) T

answered Nov 13, 2009 at 5:44

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2009-11-13 05:44:52Z

2

You need to assign a table alias to the subquery:

SELECT x.username 
  FROM (SELECT username, company, position 
          FROM table1 
         WHERE username IN (SELECT username 
                              FROM members_network 
                             WHERE xscore <= 18 AND xscore >= 15)) x

The x is the table alias.

answered Nov 13, 2009 at 5:44

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

1 Comment

Tilo Mitra Over a year ago

Woops! Deleted. Thanks again Ponies.

Dan · Accepted Answer · 2009-11-13 05:45:24Z

0

Based on the comment it appears that you need to add aliases on your subqueries. I haven't yet used a database that does this, but I guess some do:

SELECT username from (SELECT username, company, position FROM table1 WHERE username IN (SELECT username FROM members_network WHERE xscore <= 18 AND xscore >= 15) AS USERNAMES) AS USERDETAILS

Something like that ought to work (unless your database has funky alias syntax).

answered Nov 13, 2009 at 5:45

Dan

11.1k7 gold badges55 silver badges82 bronze badges

Collectives™ on Stack Overflow

SQL SubQuery getting particular column

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related