SQLzoo JOIN Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQLzoo JOIN Help

Author

Topic

Lockon
Starting Member

2 Posts

Posted - 2009-01-09 : 20:00:30

Hi,

New to SQL and I am working on some online tutorial material.

http://sqlzoo.net/3.htm

4b.

Is this possible to do this without nested select statements?

Is it possible to create an alias for the table that results from joining 2 or more tables, for use in a nested select statement?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 20:29:22

Can we know what you tried?

Lockon
Starting Member

2 Posts

Posted - 2009-01-09 : 21:31:17

This is verbose/redundant, but it supposedly produces the correct answer:

SELECT
movie.title,
actor.name
FROM
casting JOIN movie
on casting.movieid = movie.id
JOIN actor
on casting.actorid = actor.id
WHERE
casting.ord = 1 AND movie.id IN
(SELECT movie.id FROM
casting JOIN movie
on casting.movieid = movie.id
JOIN actor
on casting.actorid = actor.id
WHERE actor.name = 'Julie Andrews');

The impression that I get from reading is that nesting should be avoided, but I am not really seeing any other way to do it.

Thanks for responding.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 22:33:19

quote:
Originally posted by Lockon

This is verbose/redundant, but it supposedly produces the correct answer:

SELECT
movie.title,
actor.name
FROM
casting Inner JOIN movie
on casting.movieid = movie.id
Inner JOIN actor
on casting.actorid = actor.id
WHERE casting.ord = 1 AND actor.name = 'Julie Andrews';

The impression that I get from reading is that nesting should be avoided, but I am not really seeing any other way to do it.

Thanks for responding.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources