CODEWITHSUNDEEP
sqlmysql
Tilo Mitra
Tilo Mitra

Reputation: 2793

SQL SubQuery getting particular column

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!

Upvotes: 0

Views: 239

Answers (3)

Dan
Dan

Reputation: 11069

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 2

Shannon Severance
Shannon Severance

Reputation: 18410

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

Upvotes: 2

Related Questions