2

I have an interface in which I will show a list of items from a SQLite database that each item can be one of two types: SubItem1 and SubItem2. This database currently has three tables: Items, SubItem1 and SubItem2.

The table Items contains the columns SubItemId and Type (0 - SubItem1; 2 - SubItem2)

SubItem1 and SubItem2 have different columns, but a good amount of them are the same.

So to use populate this list I am using a query like:

select i.*, s1.name, s2.name, s1.time, s2.place
from ITEMS i
left outer join sub1 s1 on (i.type = 0 and i.sub_id = s1.id)
left outer join sub2 s2 on (i.type = 1 and i.sub_id = s2.id)

I used these columns as an example, but I am selecting around 10 columns of each SubItem table.

With this query I am getting a lot of redundant rows. For instance, when the Type is SubItem1 for a particular Item, I will also be receiving the null columns of the table SubItem2

Is there a more efficient way to make this query?

Thanks.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Lucas Tonon
  • 35
  • 5

2 Answers2

1

Use COALESCE() for the "same" columns :

select i.*, COALESCE(s1.name,s2.name) as name,
            COALESCE(s1.col1,s2.col2) as col2,
            ....
       s1.time, s2.place
from ITEMS i
left outer join sub1 s1 on (i.type = 0 and i.sub_id = s1.id)
left outer join sub2 s2 on (i.type = 1 and i.sub_id = s2.id)
sagi
  • 40,026
  • 6
  • 59
  • 84
1

You can select both types seperately with inner joins, and then combine both results with a compound query:

SELECT i.*, s.name, s.time, NULL AS place
FROM Items AS i
JOIN Sub1  AS s ON i.sub_id = s.id

UNION ALL

SELECT i.*, s.name, NULL,   s.place
FROM Items AS i
JOIN Sub2  AS s ON i.sub_id = s.id;

These are likely to be more efficient.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for opening my mind about using UNION. But the results showed a slight performance gain when not using UNION. – Lucas Tonon Dec 08 '16 at 12:06