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
 Question around foreign keys

Author  Topic 

chrisp7505
Starting Member

2 Posts

Posted - 2014-03-07 : 15:05:46
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

38200 Posts

Posted - 2014-03-07 : 15:12:12
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

2 Posts

Posted - 2014-03-07 : 15:30:43
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
   

- Advertisement -