How to JOIN on a one ot many relationship

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 2005 Forums

Transact-SQL (2005)

How to JOIN on a one ot many relationship

Author

Topic

capella07
Starting Member

46 Posts

Posted - 2007-11-09 : 09:29:21

I have a table listing movies with (among other things) multiple actors in each movie referenced by the ActorID from an Actors table.

Here's part of my (non-working) SELECT statement:

SELECT
m.MovieID,
m.Title,
m.Act1ID,
a.actFName + ' ' + a.actLName AS Actor1,
m.Act2ID,
a.actFName + ' ' + a.actLName AS Actor2,
m.Act3ID,
a.actFName + ' ' + a.actLName AS Actor3,
m.Act4ID,
a.actFName + ' ' + a.actLName AS Actor4
FROM Movies m
LEFT JOIN Actors a ON m.Act1ID = a.ActID
--With below ANDs, get NULL for all names
--AND m.Act2ID = a.ActID
--AND m.Act3ID = a.ActID
--AND m.Act4ID = a.ActID
--With below additional LEFT JOINS on the Actors table, get error:
--    The correlation name 'a' is specified multiple times in a FROM clause.
LEFT JOIN Actors a ON m.Act2ID = a.ActID
LEFT JOIN Actors a ON m.Act3ID = a.ActID
LEFT JOIN Actors a ON m.Act4ID = a.ActID
ORDER BY m.Title

You can get an idea of the structure of the tables from the code above.
Basically it's:
MOVIE table
MovieID int, PKEY
Title varchar(50)
Act1ID int
Act2ID int
Act3ID int
Act4ID int

ACTOR table
ActID int, PKEY
ActFName varchar(50)
ActLName varchar(50)

You may be able to tell by the code above, but my question is, how do I get multiple rows (Actor records) from the Actors table for the same row (Movie record) in the Movies table?

I want my query result to be something like:
(MovieID - From Movies table): 1
(Title - From Movies table): Star Wars: Episode IV - A New Hope
(Act1ID - From Movies table): 1
(Actor1 - From Actors table): Mark Hamil
(Act2ID - From Movies table): 2
(Actor2 - From Actors table): Harrison Ford
(Act3ID - From Movies table): 3
(Actor3 - From Actors table): Carrie Fischer

Any help will be greatly appreciated!

Thanks

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 09:32:52

more like...


SELECT
m.MovieID,
m.Title,
m.Act1ID,
a1.actFName + ' ' + a1.actLName AS Actor1,
m.Act2ID,
a2.actFName + ' ' + a2.actLName AS Actor2,
m.Act3ID,
a3.actFName + ' ' + a3.actLName AS Actor3,
m.Act4ID,
a4.actFName + ' ' + a4.actLName AS Actor4
FROM Movies m
LEFT JOIN Actors a1 ON m.Act1ID = a1.ActID
LEFT JOIN Actors a2 ON m.Act2ID = a2.ActID
LEFT JOIN Actors a3 ON m.Act3ID = a3.ActID
LEFT JOIN Actors a4 ON m.Act4ID = a4.ActID
ORDER BY m.Title

capella07
Starting Member

46 Posts

Posted - 2007-11-12 : 09:14:28

Yup - thanks, elancaster. I had a lightbulb moment about 2am this morning and did it that way.

=====================================
f u cn rd ths, u cn gt a gd jb n prgrmng

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources