• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 727
  • Last Modified:

Need SQL syntax help with an outer join and multiple tables

My current SQL string:

SELECT  videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM videos, music, musicArtists, peopleInVideos, athletes
WHERE videos.showVideo = 'true'
AND videos.songID = music.songID
AND music.artistID = musicArtists.artistID
AND peopleInVideos.athleteID = athletes.athleteID
AND videos.videoID = peopleInVideos.videoID
ORDER BY videos.videoID

...this works fine except that some of the entries in the videos table don't have a corresponding entry for videoID in the peopleInVideos table, so these entries don't show up if I use the above query...

So I rewrote part of the query to use a LEFT OUTER JOIN (my first attempt at using this SQL command):

SELECT videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length
FROM videos LEFT OUTER JOIN peopleInVideos
ON videos.videoID = peopleInVideos.videoID
WHERE videos.showVideo = 'true'
ORDER BY videos.videoID

...and this gives me what I want (for all the videos to show up, whether their videoID is in the peopleInVideos table or not), except I can't figure out how to plug this LEFT OUTER JOIN query into my original longer SQL string (at top).

Thanks in advance for any syntactical help.
0
willcheck
Asked:
willcheck
  • 7
  • 4
  • 3
2 Solutions
 
aaaaaaCommented:
SELECT  videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM videos, music, musicArtists, peopleInVideos, athletes
WHERE videos.showVideo = 'true'
AND videos.songID = music.songID
AND music.artistID = musicArtists.artistID
AND peopleInVideos.athleteID = athletes.athleteID
AND videos.videoID *= peopleInVideos.videoID
0
 
Anthony PerkinsCommented:
Try this using a more orthodox syntax:

SELECT      videos.videoID,
                  videoFilename,
                  videoTitle,
                  showVideo,
                  videoThumb,
                  videos.length,
                  songTitle,
                  artistName,
                  musicArtists.URL,
                  firstname,
                  lastname
FROM            videos
                  Inner Join music On videos.songID = music.songID
                  Inner Join musicArtists On music.artistID = musicArtists.artistID
                  Left Join peopleInVideos On videos.videoID = peopleInVideos.videoID
                  Left Join athletes On peopleInVideos.athleteID = athletes.athleteID
WHERE            videos.showVideo = 'true'
ORDER BY videos.videoID

Notice that you also have to LEFT JOIN athletes
0
 
willcheckAuthor Commented:
aaaaaa:

Your string generates this message:

The table 'peopleInVideos' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.



acperkins:

Your string generates the exact same results as my first string:

SELECT  videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM videos, music, musicArtists, peopleInVideos, athletes
WHERE videos.showVideo = 'true'
AND videos.songID = music.songID
AND music.artistID = musicArtists.artistID
AND peopleInVideos.athleteID = athletes.athleteID
AND videos.videoID = peopleInVideos.videoID
ORDER BY videos.videoID


...any other ideas?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
aaaaaaCommented:
try this:

SELECT  videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM videos, music, musicArtists, peopleInVideos, athletes
WHERE videos.showVideo = 'true'
AND videos.songID *= music.songID
AND music.artistID = musicArtists.artistID
AND peopleInVideos.athleteID = athletes.athleteID
AND videos.videoID *= peopleInVideos.videoID
0
 
Anthony PerkinsCommented:
>>Your string generates the exact same results as my first string<<
Than I suspect there is something that you are not telling us. Such as what is the current result and what is the desired result.
0
 
Anthony PerkinsCommented:
Sure you can do a LEFT JOIN on all the tables as in:
SELECT     videos.videoID,
               videoFilename,
               videoTitle,
               showVideo,
               videoThumb,
               videos.length,
               songTitle,
               artistName,
               musicArtists.URL,
               firstname,
               lastname
FROM          videos
               LEFT Join music On videos.songID = music.songID
               LEFT Join musicArtists On music.artistID = musicArtists.artistID
               LEFT Join peopleInVideos On videos.videoID = peopleInVideos.videoID
               LEFT Join athletes On peopleInVideos.athleteID = athletes.athleteID
WHERE          videos.showVideo = 'true'
ORDER BY videos.videoID

But that was not in your original requirements.
0
 
aaaaaaCommented:
or this:

SELECT  a.videoID, videoFilename, videoTitle, showVideo, videoThumb, a.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM (SELECT videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,            
                      peopleInVideos.athleteID
           FROM videos LEFT OUTER JOIN peopleInVideos
           ON videos.videoID = peopleInVideos.videoID
           WHERE videos.showVideo = 'true'
           ORDER BY videos.videoID
          ) a, music, musicArtists, athletes
where a.songID = music.songID
AND music.artistID = musicArtists.artistID
AND a.athleteID = athletes.athleteID
0
 
Anthony PerkinsCommented:
Do you get the drift that we have not a clue based on your requirements and we are just thrwing out queries? You are right.
0
 
willcheckAuthor Commented:
aaaaaa's 2nd solutions generates an Invalid column name 'songID' error.

Anybody else wanna give this a stab?
0
 
Anthony PerkinsCommented:
>>aaaaaa's 2nd solutions generates an Invalid column name 'songID' error.<<
All you have to do is include SongID in the derived table, as in:
FROM (SELECT videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,            
                     peopleInVideos.athleteID, video.SongID

No telling it that is going to give you the right results.
0
 
aaaaaaCommented:
ok, misstake, try this:

SELECT  a.videoID, videoFilename, videoTitle, showVideo, videoThumb, a.length,
songTitle, artistName, musicArtists.URL,
firstname, lastname
FROM (SELECT videos.videoID, videoFilename, videoTitle, showVideo, videoThumb, videos.length,            
                      peopleInVideos.athleteID, video.SongID
           FROM videos LEFT OUTER JOIN peopleInVideos
           ON videos.videoID = peopleInVideos.videoID
           WHERE videos.showVideo = 'true'
           ORDER BY videos.videoID
          ) a, music, musicArtists, athletes
where a.songID = music.songID
AND music.artistID = musicArtists.artistID
AND a.athleteID = athletes.athleteID
0
 
willcheckAuthor Commented:
OK - I'm not sure if aaaaaa's last answer works, but I figured out this in the meantime:

SELECT  videos.videoID, videos.videoFilename, videos.videoTitle, videos.showVideo, videos.videoThumb, videos.length,
music.songTitle, musicArtists.artistName, musicArtists.URL,
athletes.firstname, athletes.lastname
FROM videos
LEFT OUTER JOIN peopleInVideos ON videos.videoID = peopleInVideos.videoID
LEFT OUTER JOIN music ON videos.songID = music.songID
LEFT OUTER JOIN musicArtists ON music.artistID = musicArtists.artistID
LEFT OUTER JOIN athletes ON peopleInVideos.athleteID = athletes.athleteID
WHERE videos.showVideo = 'true'
ORDER BY videos.videoID

Thanks for tips anyway -- they helped me learn how to use JOINs a little bit better.
0
 
Anthony PerkinsCommented:
>>I'm not sure if aaaaaa's last answer works, but I figured out this in the meantime:<<
Grief!  That exactly the same as my last suggestion.

I realize you are new here, but please take the time to re-read the EE Guidelines regarding grading at:
What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73
You may find experts here more responsive if you do.
0
 
Anthony PerkinsCommented:
Here's how you can change it:

Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now