The best way to return related data in a SQL statement
- by Darvis Lombardo
I have a question on the best method to get back to a piece of data that is in a related table on the other side of a many-to-many relationship table.
My first method uses joins to get back to the data, but because there are multiple matching rows in the relationship table, I had to use a TOP 1 to get a single row result.
My second method uses a subquery to get the data but this just doesn't feel right.
So, my question is, which is the preferred method, or is there a better method?
The script needed to create the test tables, insert data, and run the two queries is below.
Thanks for your advice!
Darvis
--------------------------------------------------------------------------------------------
-- Create Tables
--------------------------------------------------------------------------------------------
DECLARE @TableA TABLE (
[A_ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) NULL)
DECLARE @TableB TABLE (
[B_ID] [int] IDENTITY(1,1) NOT NULL,
[A_ID] [int] NOT NULL,
[Description] [varchar](50) NOT NULL)
DECLARE @TableC TABLE (
[C_ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) NOT NULL)
DECLARE @TableB_C TABLE (
[B_ID] [int] NOT NULL,
[C_ID] [int] NOT NULL)
--------------------------------------------------------------------------------------------
-- Insert Test Data
--------------------------------------------------------------------------------------------
INSERT INTO @TableA VALUES('A-One')
INSERT INTO @TableA VALUES('A-Two')
INSERT INTO @TableA VALUES('A-Three')
INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-One')
INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-Two')
INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-Three')
INSERT INTO @TableB (A_ID, Description) VALUES(2,'B-Four')
INSERT INTO @TableB (A_ID, Description) VALUES(2,'B-Five')
INSERT INTO @TableB (A_ID, Description) VALUES(3,'B-Six')
INSERT INTO @TableC VALUES('C-One')
INSERT INTO @TableC VALUES('C-Two')
INSERT INTO @TableC VALUES('C-Three')
INSERT INTO @TableB_C (B_ID, C_ID) VALUES(1, 1)
INSERT INTO @TableB_C (B_ID, C_ID) VALUES(2, 1)
INSERT INTO @TableB_C (B_ID, C_ID) VALUES(3, 1)
--------------------------------------------------------------------------------------------
-- Get result - method 1
--------------------------------------------------------------------------------------------
SELECT TOP 1 C.*, A.Description
FROM @TableC C
JOIN @TableB_C BC ON BC.C_ID = C.C_ID
JOIN @TableB B ON B.B_ID = BC.B_ID
JOIN @TableA A ON B.A_ID = A.A_ID
WHERE C.C_ID = 1
--------------------------------------------------------------------------------------------
-- Get result - method 2
--------------------------------------------------------------------------------------------
SELECT C.*,
(SELECT A.Description
FROM @TableA A
WHERE EXISTS (SELECT *
FROM @TableB_C BC
JOIN @TableB B ON B.B_ID = BC.B_ID
WHERE BC.C_ID = C.C_ID AND B.A_ID = A.A_ID))
FROM @TableC C
WHERE C.C_ID = 1
