Need help with table join - 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 2005 Forums

Transact-SQL (2005)

Need help with table join

Author

Topic

capella07
Starting Member

46 Posts

Posted - 2009-05-22 : 16:00:13

Hello, everyone

I have a database for movies and in it there are tables for (among other things) the movie, and actors, directors. The movie table has foreign key columns for the IDs of multiple directors & actors. i.e. the movies table columns include movieID, title, directorID1, directorID2, actorID1, actorID2. And the actor table, for instance has these columns: actorID, actorFName, actorLName

I've got a working query that returns the concatenated names from the relevant tables, but when a movie record has one of the director or actor columns NULL then that record doesn't show at all.

Here's my working query:

SELECT
	m.title,
	m.runningTime,
	m.yearReleased,
	g.genre,
	f.format,
	d1.directorFName + ' ' + d1.directorLName AS director1,
--CASE
--	WHEN t.directorID2 IS NOT NULL THEN
--	d2.directorFName + ' ' + d2.directorLName
--END AS director2,
	a1.actorFName + ' ' + a1.actorLName AS actor1,
	a2.actorFName + ' ' + a2.actorLName AS actor2,
	a3.actorFName + ' ' + a3.actorLName AS actor3
--	a4.actorFName + ' ' + a4.actorLName AS actor4
FROM movie m
INNER JOIN genre g ON m.genreID = g.genreID
INNER JOIN format f ON m.formatID = f.formatID
INNER JOIN director d1 ON m.directorID1 = d1.directorID
--INNER JOIN director d2 ON d2.directorID = t.directorID2
INNER JOIN actor a1 ON a1.actorID = m.actorID1
INNER JOIN actor a2 ON a2.actorID = m.actorID2
INNER JOIN actor a3 ON a3.actorID = m.actorID3
--INNER JOIN actor a4 ON a4.actorID = t.actorID4
ORDER BY m.title

The CASE was an attempt to deal with a movie record that had a NULL for the directorID2 column. That didn't work, but I'm wondering if the CASE statement is okay and it didn't work because of the JOIN that referenced the directorID2 column.

How can I deal with records that have NULL fields in this query?

Thanks!

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-22 : 17:31:20

If I am reading your situation correctly, you will need to use a LEFT OUTER JOIN. That should allow you to get an actor if they exist but not prevent the movie record from coming back if it does not (like the INNER JOIN is doing).

capella07
Starting Member

46 Posts

Posted - 2009-05-22 : 17:35:27

Thanks a mil, Lamprey! That did it. I still have a hard time distinguishing the differences between LEFT, RIGHT, INNER, and OUTER joins.

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-22 : 17:47:47

I dunno if this will help, but here is a link to a little sample that I use to illistrate the difference between the INNER and LEFT joins and what affect putting the restriction on the join or in the WHERE clause can have:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98481#374748

capella07
Starting Member

46 Posts

Posted - 2009-05-22 : 17:53:39

Cool! Thanks for the link.

=====================================
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