sql server 2005 - return single row when 2 records in right table

Question

Hi, I have two related sql server tables ... TableA and TableB.

***TableA - Columns***
TableA_ID     INT
VALUE         VARCHAR(100)

***TableB - Columns***
TableB_ID     INT
TableA_ID     INT
VALUE         VARCHAR(100)

For every single record in TableA there are always 2 records in TableB. Therefore TableA has a one-to-many relationship with TableB.

How could I write a single sql statement to join these tables and return a single row for each row in TableA that includes:

• a column for the VALUE column in the first related row in table B
• a column for the VALUE column in the second related row in table B?

Thanks.

Answer 1

For exactly 2 related records, that's easy. Join table B twice:

SELECT 
  A.TableA_ID,
  A.VALUE      AS VALUE_A
  B1.VALUE     AS VALUE_B1
  B2.VALUE     AS VALUE_B2
FROM
  TableA AS A
  INNER JOIN TableB B1 ON B1.TableA_ID = A.TableA_ID
  INNER JOIN TableB B2 ON B2.TableA_ID = A.TableA_ID
WHERE
  B1.TableB_ID < B2.TableB_ID

If you have a column in table B that determines what is "first value" and what is "second value", this gets even easier (and it would work like this for N columns in table B, just add more joins):

SELECT 
  A.TableA_ID,
  A.VALUE      AS VALUE_A
  B1.VALUE     AS VALUE_B1
  B2.VALUE     AS VALUE_B2
FROM
  TableA AS A
  INNER JOIN TableB B1 ON B1.TableA_ID = A.TableA_ID AND B1.Type = '1'
  INNER JOIN TableB B2 ON B2.TableA_ID = A.TableA_ID AND B2.Type = '2'

A composite index on TableB over (TableA_ID, Type) helps this join.