SQL SubQuery getting particular column

Question

Hi everyone,

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!

Answer 1

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

Answer 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.

Answer 3

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