CREATE TABLE owner
(
own_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
own_name VARCHAR(20)
)
CREATE TABLE mobile_stock
(list_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
list_name VARCHAR(20))
CREATE TABLE mobile_wanted
(mob_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
mob_name VARCHAR(20))
CREATE TABLE junction1
(
own_id INT,
mob_id INT,
primary key (own_id, mob_id),
CONSTRAINT mobile_mob_id_fk
FOREIGN KEY (mob_id)
REFERENCES mobile_wanted(mob_id),
CONSTRAINT owner_own_id_fk
FOREIGN KEY (own_id)
REFERENCES owner(own_id)
)
There are 4 tables here :
owner
own_id......own_name
1..............Jack
2..............Sawyer
3..............Michael
mobile_wanted
mob_id.......mob_name
1...............Sony
2...............Nokia
3...............iPhone
owner has a multi-table relationship with mobile_wanted.
So i created a junction table junction1.
junction1
own_id.......mob_id
1...............1
2...............1
3...............3
mobile_stock gives this names of the mobiles presently available.
Query :
How can i query the first 3 tables to get the own_name along with mob_name and hence get the names and the respective mobile they desire to own ?
The Resultant table should look like this :
own_name.......mob_name
Jack................Sony
Sawyer............Sony
Michael............iPhone