SQL squash rows returned when joining several tables

join;sqlite

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?

Best Answer

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

Related Solutions

Sql-server – Joining multiple tables results in duplicate rows

Presumably, you want to see a single entry for each unique person/address/email/website combination. If so, try this:

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].[state], '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
FROM dbo.Person
    LEFT JOIN dbo.PersonAddress ON Person.ID = PersonAddress.Person
    LEFT JOIN dbo.[Address] ON PersonAddress.[Address] = [Address].ID
    LEFT JOIN dbo.PersonPhone ON Person.ID = PersonPhone.Person
    LEFT JOIN dbo.Phone ON PersonPhone.Person = Phone.ID
    LEFT JOIN dbo.Email WITH (NOLOCK) ON Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and [Address] is not null
    and name like '%Mike%'
GROUP BY (person.FirstName + ' ' + person.LastName)
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].state, '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
ORDER BY isClient asc;

The GROUP BY clause at the end ensures only a single row is returned for each unique combination of columns in the GROUP BY clause. This should prevent duplicate rows being displayed in your results.

A couple of things to note:

Always use the schema qualifier on the FROM clause. FROM Person should be FROM dbo.Person -> this eliminates any confusion if you introduce new schemas in the future, and prevents the query optimizer from having to look for the default schema for your user.
For maintainability in the future, you probably want to name columns the same regardless of which table they are in. So for instance, instead of the ID column in the People table being named ID, and it being named Person in the Address table, I'd name it PersonID in both tables. This prevents confusion (read bugs) in joins such as dbo.Person LEFT JOIN dbo.Address ON Person.ID = Address.Person.
Instead of naming tables like Person, they should be named after the collection of items they contain, in plural. So, Person becomes People, and Address becomes Addresses. This eliminates confusion -> does the Address table actually contain a single address or multiple addresses?
WITH (NOLOCK) should be avoided like the plague it is unless you fully understand the consequences of reading rows that have been modified by other transactions but not yet committed. From MSDN:

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

The SNAPSHOT isolation level.

More satisfactory query to get the rows of ‘A’ ordered by the number of rows of ‘B’ associated with that row of A in SQL

This seems totally sufficient:

 Select A.ID, 
        A.Name, 
        A.Age, 
        Count(B.color) AS Count
    From B 
    Left Join A
         On B.AID=A.ID
    Group By A.ID, A.Name, A.Age
    Order By Count(B.color) Desc

It does leave ambiguous any subsequent level of ordering beyond color count, but that's easy enough to customize by adding to the Order clause.

Best Answer

Related Solutions

Sql-server – Joining multiple tables results in duplicate rows

More satisfactory query to get the rows of ‘A’ ordered by the number of rows of ‘B’ associated with that row of A in SQL

Related Question