CODEWITHSUNDEEP
sqloracle-databasesubquery
Ivan
Ivan

Reputation: 315

SQL Query with a subquery

I need to retrieve the player number, name, street and town for all players that live in the same street and town as the players with number 6 or 7;

I am not sure how to write the SQL Query, i think i need to have a subquery in the WHERE clause but can't figure out how to do it.

This is what i came up with, but i have no way of testing it at this point

SELECT playerNo, name, street, town
FROM Players
WHERE street IN ( SELECT street, playerNo
                  FROM Players
                  WHERE playerNo IN (6,7));
AND town IN (SELECT town, playerNo
             FROM Players
             WHERE playerNo IN (6,7));

Should be compatible with Oracle 10g

Thanks to everyone who replied!

Upvotes: 1

Views: 898

Answers (3)

user330315
user330315

Reputation:

You didn't state your DBMS so this is the ANSI SQL solution (which works in Oracle, PostgreSQL, DB2, Teradata, MySQL and probably a lot of others as well):

SELECT playerNo, name, street, town
FROM Players
WHERE (street, town) IN (SELECT street, town
                         FROM Players
                         WHERE playerNo IN (6,7));

A side note regarding the IN operator:

your the expression town IN (SELECT town, playerNO ... is invalid because the subselect must the exact same number of columns as the left hand side of the IN operator. In your case you would have to write town IN (SELECT town FROM ...)

Upvotes: 4

Andrew
Andrew

Reputation: 4624

SELECT p1.playerNo, p1.name, p1.street, p1.town
FROM Players AS p1
INNER JOIN Players AS p2 ON p2.street = p1.street AND p2.town = p1.town
WHERE p2.playerNo IN (6,7)

It's often best to avoid subqueries because the database optimizer can't 'see inside the parens'.

Upvotes: 2

aF.
aF.

Reputation: 66687

Something like this should do the trick:

select t.playerNumber, t.name, t.street, t.town
from tablename t
inner join (select street, town from tablename where playerNumber in (6,7)) aux on aux.street = t.street and aux.town = t.town

Upvotes: 2

Related Questions