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.
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.FilmsDirectorsADD CONSTRAINT fkFilmsDirectorsWithFilms FOREIGN KEY (FilmID) REFERENCES Dvd.Films (FilmID)GOALTER TABLE Dvd.FilmsDirectorsADD CONSTRAINT fkFilmsDirectorsrsWithDirectors FOREIGN KEY (DirectorID) REFERENCES Dvd.Directors (DirectorID)GOWhat 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.directoridfrom dvd.filmsjoin dvd.filmsdirectors ON dvd.films.filmid=dvd.filmsdirectors.filmidwhere dvd.filmsdirectors.directorid='2' If this was oracle I would use something like thisSELECT 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
|
|
|
|
|