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
 Combining Seperate Tables

Author  Topic 

lassise
Starting Member

8 Posts

Posted - 2012-12-05 : 17:08:09
I am making a database for DVD collectors. I have 2 tables (members, DVD) each with primary keys (memberID, DVDID). I want to find a way to add users & DVDs together. So if memberID 5 owns DVDID 7. I want to be able to display First, Last, Email (all from members table), DVDTitle, Director (all from DVD table). Should I make a third table?

lassise
Starting Member

8 Posts

Posted - 2012-12-05 : 17:11:52
Here is the creation for the tables I have

create table members
( memberID int not null Primary Key identity,
first varchar(25) NOT NULL,
last varchar(25) NOT NULL,
email varchar(30) NOT NULL,
DVDCollection int);

create table DVD
( DVDID int not null Primary Key identity,
Title varchar(50) NOT NULL,
Director varchar(25) NOT NULL)
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-05 : 17:45:34
Yes junction table to normalize m-m relationship.
Go to Top of Page

lassise
Starting Member

8 Posts

Posted - 2012-12-05 : 19:59:10
Sorry, what exactly does that mean? / How do I do that?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-05 : 20:00:48
Create MemberDVD table with primary key from both table
Go to Top of Page

lassise
Starting Member

8 Posts

Posted - 2012-12-05 : 20:17:13
Actually, I looked it up, thanks for your help. For people with a similar problem, I created a third table referencing both primary keys



create table DVDCollection
(
memberID int NOT NULL,
DVDID int NOT NULL,
CONSTRAINT PK_DVDCollectionTable PRIMARY KEY
(
memberID,
DVDID
),
FOREIGN KEY (memberID) REFERENCES members (memberID),
FOREIGN KEY (DVDID) REFERENCES dvd (DVDID))

And then you can query it like so

select first, last, Title from dvdcollection
join members on members.memberid = dvdcollection.memberID
join DVD on dvd.dvdid = dvdcollection.DVDID
Go to Top of Page
   

- Advertisement -