?
Solved

Need SQL syntax help with an outer join and multiple tables

Posted on 2005-05-04
14
Medium Priority
?
724 Views
Last Modified: 2007-12-19
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
Comment
Question by:willcheck
  • 7
  • 4
  • 3
14 Comments
 
LVL 4

Expert Comment

by:aaaaaa
ID: 13932307
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932360
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
 

Author Comment

by:willcheck
ID: 13932485
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:aaaaaa
ID: 13932508
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932514
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932517
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
 
LVL 4

Expert Comment

by:aaaaaa
ID: 13932526
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 13932546
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
 

Author Comment

by:willcheck
ID: 13932615
aaaaaa's 2nd solutions generates an Invalid column name 'songID' error.

Anybody else wanna give this a stab?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932659
>>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
 
LVL 4

Accepted Solution

by:
aaaaaa earned 250 total points
ID: 13932728
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
 

Author Comment

by:willcheck
ID: 13932849
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932920
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13932931
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question