SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question around foreign keys
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chrisp7505
Starting Member

USA
2 Posts

Posted - 03/07/2014 :  15:05:46  Show Profile  Reply with Quote
First off this is my first post here, I hope I have this in the right forum.

I'm working with a basic dvd collection database to try and learn MS SQL. My main table dvd.films has a pk of filmid, another table dvd.directors has a pk of directorid; this table also has other director information. There is a third table that ties the two tables together called dvd.filmsdirectors; this table only contains the filmid and directorid fields.

I used two foreign keys for this (learning process)

ALTER TABLE Dvd.FilmsDirectors
ADD CONSTRAINT fkFilmsDirectorsWithFilms FOREIGN KEY (FilmID) REFERENCES Dvd.Films (FilmID)
GO

ALTER TABLE Dvd.FilmsDirectors
ADD CONSTRAINT fkFilmsDirectorsrsWithDirectors FOREIGN KEY (DirectorID) REFERENCES Dvd.Directors (DirectorID)
GO


What I would like to do is write a query to list the movies I have that Lucas was the director for.

What I have so far is below, the problem I have is trying to link the director table via the foreign link. I would prefer to use dvd.directors.director, this would produce Lucas instead of a '2'. I know I could join all three tables, but I assume since they are linked via a foreign key it should be possible.


SELECT dvd.films.title,dvd.films.disc_nbr,dvd.filmsdirectors.directorid
from dvd.films
join dvd.filmsdirectors ON dvd.films.filmid=dvd.filmsdirectors.filmid
where dvd.filmsdirectors.directorid='2'


If this was oracle I would use something like this

SELECT dvd.films.title,dvd.films.disc_nbr,directorlink@dvd.directors.director

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 03/07/2014 :  15:12:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you want data from a third table, then you need to join to that third table. A foreign key does not automatically give you the data. A foreign key is a constraint that is there to protect the data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

chrisp7505
Starting Member

USA
2 Posts

Posted - 03/07/2014 :  15:30:43  Show Profile  Reply with Quote
Thanks for the confirmation, this foreign key link is non-standard but it was introduced years ago. I thought it might have found it's way over, thanks for the information.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000