Noob Doubt: How to query a many-to-many relation Tables

• sithlordvarun

  SSC Journeyman

  Points: 96

  More actions

  March 27, 2010 at 11:38 pm

  #217193

  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

• Paul White

  SSC Guru

  Points: 150468

  More actions

  March 28, 2010 at 6:45 am

  #1141077

  SELECT O.own_name, MW.mob_name

  FROM owner O

  JOIN junction1 J

  ON J.own_id = O.own_id

  JOIN mobile_wanted MW

  ON MW.mob_id = J.mob_id;

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi