1

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?

1

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
  • Thank you for your answer, unfortunately I had missed to mention anywhere that the database is currently in sqlite, which for instance does not support ROW_NUMBER() or FULL OUTER JOIN. Perhaps I should consider migrating the data to postgresql to support this kind of query? And does this generalize well to up to lets say 10 attribute tables? – slj Oct 2 '18 at 9:43
  • @slj: According to the manual, SQLite supports window functions, including ROW_NUMBER(). – Andriy M Oct 2 '18 at 9:56
  • does not support ... FULL OUTER JOIN Emulate it by A LEFT JOIN B UNION ALL B LEFT JOIN A [WHERE A.id IS NULL]. – Akina Oct 2 '18 at 10:35
  • Ok, sorry for making incorrect conclusions about support, I have never worked with databases before... Still, when trying this query in python with sqlite3, I get OperationalError: near "(": syntax error. A search on stackoverflow gives me the reason that row_number() is not supported by sqlite. More search yields that window functions were introduced in sqlite3 v3.25, I only have v2.6 available. Is there any possibility of doing this without row_number()? – slj Oct 2 '18 at 11:52
  • @slj: The problem here is that an individual row from B has no relationship with an individual row from C and yet you want to place two unrelated values, B.id and C.id, on the same row in the output. The ROW_NUMBER() trick suggested by Akina here is meant to create such a relationship, so that you can can connect two rows that have the same item_id reference and the same row number. In absence of the row number, there's no connection. Unless there's something else you forgot to mention, of course. – Andriy M Oct 3 '18 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.