SQL squash rows returned when joining several tables I have a database which has a table for the main items, lets call it A with primary key 'id'.
Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have 0 or more of. Each row in the attribute tables have a foreign key ('item_id'), specifing which main item it corresponds to, they also have a primary key 'id' for identification purposes.

I'm attempting to make a query which will get me all items with all attributes from a selection of the attribute tables. For instance if I want information about all attributes B and C, I tried something like

SELECT A.id, B.id, C.id 
FROM A
LEFT JOIN B ON A.id=B.item_id
LEFT JOIN C ON A.id=C.item_id


However for an item which has 2 attributes in B and 3 in C, this returns 6 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     1  |     2   |
|    1  |     1  |     3   |
|    1  |     2  |     1   |
|    1  |     2  |     2   |
|    1  |     2  |     3   |
|    2  |  NULL  |     1   |
.                          
. 
.


While in the best of worlds it would only require 3 rows for that item:

----------------------------
| A.id  |  B.id  |  C.id   |
----------------------------
|    1  |     1  |     1   |
|    1  |     2  |     2   |
|    1  |  NULL? |     3   |
|    2  |  NULL  |     1   |
.                          
. 
.


Is there anyway to "squash" the rows in this way, so that the number of rows returned for each item is the maximum of number of attributes in the related tables for the item (or at least one row with NULL attributes if it doesn't have any)?
The number of returned rows for each item can grow exponentially with the number of attributes requested...

Or is the best way to gather the attribute information to make one query for each attribute table, with only one JOIN in each query?
Something like

WITH BN AS (SELECT id, 
                   item_id, 
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn 
            FROM B),
     CN AS (SELECT id, 
                   item_id, 
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn 
            FROM C)
SELECT A.id, BC.id1, BC.id2
FROM A
LEFT JOIN (SELECT BN.item_id item_id, BN.id id1, CN.id id2
           FROM BN 
           FULL OUTER JOIN CN ON BN.item_id = CN.item_id
                             AND BN.rn = CN.rn ) BC ON A.id = BC.item_id