Many-to-Many Join Example, Sql Server

Many-to-Many Join Example

CREATE TABLE table1 (
  Item_main_SID INT PRIMARY KEY IDENTITY (1, 1),
  item_main_text VARCHAR (255) NOT NULL,
);

INSERT INTO table1 (item_main_text)
VALUES ('Row 1'), ('Row 2'), ('Row 3')

CREATE TABLE table3 (
  R_rfed_SID INT PRIMARY KEY IDENTITY (1, 1),
  rfed_desc VARCHAR (255) NOT NULL,
);

INSERT INTO table3 (rfed_desc)
VALUES ('Rfed Row 1'), ('Rfed Row 2'), ('Rfed Row 3')

CREATE TABLE table1_to_table3 (
  linkID INT PRIMARY KEY IDENTITY (1, 1),
  Item_main_SID INT NOT NULL,
  R_rfed_SID INT NOT NULL,

CONSTRAINT [FK_Item_main_SID] FOREIGN KEY ([Item_main_SID])  REFERENCES [table1]([Item_main_SID]),
  CONSTRAINT [FK_R_rfed_SID] FOREIGN KEY ([R_rfed_SID])  REFERENCES [table3]([R_rfed_SID])
);

-- Relate Item_Main to Rfed ID's (e.g: ID 1 -> rfed ID 1)
INSERT INTO table1_to_table3 (Item_main_SID, R_rfed_SID)
VALUES (1,1), (1,2), (1,3), (2,2), (3,1),(3,3)

-- Show bridge tables to validate Join results 
-- Item_main ID 1 should only return Rfed Row 1, 2, 3
-- Item_main ID 2 should only return Rfed Row 2
-- Item_main ID 3 should only return Rfed Row 1, 3

SELECT linkID as [M2M ID], Item_main_SID, R_rfed_SID
FROM table1_to_table3

-- Select Fields and rename them
SELECT table1.[Item_main_SID] as table1_ID, table3.[rfed_desc] as [Related rfed Description]

-- Reference Bridge table for Many-to-Many relationships to know what index 
-- in Table 1 and 3 to retrieve selected values
FROM table1_to_table3

INNER JOIN table1
  -- Join Table 1 values selected based on matches found in bridge table
      ON table1_to_table3.[Item_main_SID] = table1.[Item_main_SID]
  INNER JOIN table3 
  -- Join Table 3 values selected based on matches found in bridge table
      ON table1_to_table3.[R_rfed_SID] = table3.[R_rfed_SID]


run \| edit \| history \| help	0