Advanced Joins - 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

General SQL Server Forums

New to SQL Server Programming

Advanced Joins

Author

Topic

Hobbes13
Starting Member

2 Posts

Posted - 2006-05-11 : 00:10:30

Here's a challenge for one of those database experts out there...

I want to join two tables in a Left Join format, with the first table being the one referencing the second table. Easy enough right? Well, I also want to be able to reference this second table from 3 different fields.
For example:

My tables
#1: video
id, description ...etc... award1, award2, award3

#2: award
id, description, image, etc...

As you can see, I want to be able to award more than 1 award from the "award" table to each video in the "video" table. I then want to be able to assign a variable to each field from the result of the SQL query so I can display all the information on one page for every video with every combination of possible awards.

I also, in my SQL Query, want a WHERE clause:
WHERE video.id = $vidid (a variable from a previous page so this page only displays the video I want)

My mind says surely this must be possible, but I have no idea how to go about it. Can anyone give me any hints/help/pointers/advice on this? Even if this requires two SQL queries, I may be able to work with that, but I need to know. If I'm posting at the wrong place, or nobody here knows how to do this, then I would also really appreciate being pointed in the right direction.

Thanks in advance to anyone who even reads this and thinks over it. This is for a free online fan site http://www.lostvideo.net/ that I'm helping with some redesigns.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-11 : 01:39:30

[code]SELECT V.description
,A1.description AS A1description
,A1.image AS A1image
,A2.description AS A2description
,A2.image AS A2image
,A3.description AS A3description
,A3.image AS A3image
FROM video V
LEFT OUTER JOIN award A1
ON A1.id = V.award1
LEFT OUTER JOIN award A2
ON A2.id = V.award2
LEFT OUTER JOIN award A3
ON A3.id = V.award3
WHERE video.id = $vidid[/code]
this should get you started. I would sugest though, that you normalize the data, and I am sure a movie can recieve more than three awards.

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Hobbes13
Starting Member

2 Posts

Posted - 2006-05-11 : 01:55:06

Thank you thank you for the reply! I see what that is now, okay, I'll try that out right now and hopefully get it working.

Thanks for giving a newbie a push in the right direction! :D

EDIT: Got it all working beautifully! You are a God!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources