SQL Query with a subquery

Question

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!

You can test your query at SQL Fiddle, so next time you won't have "no way of testing it." You'll see, for example, that it doesn't run: sqlfiddle.com/#!4/b5ad1/1, but that one of the answers below does. sqlfiddle.com/#!4/b5ad1/3 — Steve Kass
– Steve Kass, Commented May 17, 2012 at 19:33

score 4 · Accepted Answer · 2012-05-17 17:09:09Z

4

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

edited May 17, 2012 at 17:09

answered May 17, 2012 at 17:03

user330315

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

Comments

aF. · Accepted Answer · 2012-05-17 16:51:53Z

2

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

answered May 17, 2012 at 16:51

aF.

67k45 gold badges141 silver badges200 bronze badges

2 Comments

Alexander Malakhov Over a year ago

this looks really weird to me. Why do you put where inside join ? O_o

aF. Over a year ago

The "inner" select gets the street and town of the players 6 and 7. The "outer" select gets the info that have the street and town given in the "inner" select. It does the trick, but like you said it's possible in a simpler way :)

Andrew · Accepted Answer · 2012-05-17 17:05:17Z

2

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

answered May 17, 2012 at 17:05

Andrew

4,62428 silver badges31 bronze badges

2 Comments

user330315 Over a year ago

That's a nice solution as well (although I'm not entirely convinced that Oracle "can't see inside the parens").

Andrew Over a year ago

Thanks. Oracle probably is smarter than a lot of other DBs. I know I have often sped things up on other systems by refactoring a subquery. Most of the time it's more important that the thing is readable in a month.

Collectives™ on Stack Overflow

SQL Query with a subquery

3 Answers 3

Comments

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Related