How to join these tables for a full result set from all tables?

Question

I have table A of Entities

I have table B of Items

I have Table C of Entity-Items, and their associated configured values.

I'm looking to write a view that will return me a row with each combination of Entity + Item, and to use the data from table C if it exists.

In addition, if there is items in TableC that do not existing in TableB, I would like those as well

For example,

Table A 
Entity 1
Entity 2

Table B
Item X
Item Y
Item Z

Table C
Entity 1    Item X    True
Entity 1    Item Y    False
Entity 2    Item X    False
Entity 2    Item J    False


Result Table Wanted
Entity 1    Item X    True
Entity 1    Item Y    False
Entity 1    Item Z    Null
Entity 2    Item X    False
Entity 2    Item Y    Null
Entity 2    Item Z    Null
Entity 2    Item J    False

For some reason I am drawing a blank for this. It's been a while since I worked with SQL, so perhaps I'm missing something obvious. Can someone help me with identifying the syntax I need to write this query?

I've come close using CROSS JOIN

SELECT *
FROM Entities
CROSS JOIN Items
LEFT OUTER JOIN EntityItems 
    ON Entities.ID = EntityItems.EntityID
    AND Items.ID = EntityItems.ItemID

This returns me everything but the row in Table C for Item J.

Update : Scratch that, it actually returns me too many rows. That's what I'm playing with now though.

I'm using MS Sql Server 2017

Answer 1

Your cross join/left join is the right approach:

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     Items i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;

However, this assumes that ItemId is correctly defined with a foreign key relationship. You seem to have invalid ItemIds. You can fix this:

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     (SELECT i.ItemId
      FROM Items i
      UNION  -- on purpose to remove duplicates
      SELECT ei.ItemId
      FROM EntityItems ei
     ) i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;

However, I strongly recommend that you fix your data (i.e. add J to the items table) and add:

alter table entityitems add constraint fk_entityitems_entityid
    foreign key (entityid) references entities(entityid);

alter table entityitems add constraint fk_entityitems_itemid
    foreign key (itemid) references entities(itemid);

This will help you ensure data integrity (moving forward) -- after you have fixed the data.

EDIT:

Ahh, you don't want the additional item ids on all the entities. If so:

SELECT e.EntityID, i.ItemId, COALESCE(ei.value, 'false') as value
FROM Entities e CROSS JOIN
     Items i LEFT JOIN
     EntityItems ei
    ON e.ID = ei.EntityID AND
       i.ID = ei.ItemID;
UNION ALL
SELECT ei.EntityId, ei.ItemId, ei.value
FROM EntityItems ei
WHERE NOT EXISTS (SELECT 1 FROM Items i WHERE i.ItemId = ei.ItemId);