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