How to perform a 3 table join with sparse lookup table I have 3 tables companies, products, and companies_products.

companies (id, name)
products (id, name)
companies_products(company_id, product_id)


companies_products is sparse, in that the only entries in it are for products that a company has subscribed to - there is no entry for non-subscribed products). I am doing this to prevent having to update every company in the event that a new product is to be added.

Unfortunately this makes getting list of what a company is and isn't subscribed to rather difficult for me to figure out

Is there a way to query for a result from this data:

company 

(id: 1, name: "Test Company")


products 

(id: 1, name: "Product 1")
(id: 2, name: "Product 2")
(id: 3, name: "Product 3")


company_products

(company_id: 1, product_id: 1)
(company_id: 1, product_id: 2)


to get a result such as this?

(company_id: 1, product_id: 1, status: "enabled")
(company_id: 1, product_id: 2, status: "enabled")
(company_id: 1, product_id: 3, status: "disabled")
First part returns the Cartesian product of companies and products.

from       companies c
cross join products p


Then for every combination of Company - Product it checks if exists a record in the companies_products table.




select     c.id, 
           c.name, 
           p.id, 
           p.name,
           if(cp.company_id is null, 'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
left join  companies_products cp
on         cp.company_id = c.id
and        cp.product_id = p.id
;



id | name         | id | name      | status          
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: disabled
 1 | Test Company |  2 | Product 2 | status: disabled
 1 | Test Company |  3 | Product 3 | status: enabled 



dbfiddle here

Or you can use EXISTS to check if there is a record that match both identifiers.




select     c.id, 
           c.name, 
           p.id, 
           p.name,
           if (exists (select 1
                       from   companies_products cp
                       where  cp.company_id = c.id
                       and    cp.product_id = p.id) 
                      ,'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
;



id | name         | id | name      | status          
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: enabled 
 1 | Test Company |  2 | Product 2 | status: enabled 
 1 | Test Company |  3 | Product 3 | status: disabled



dbfiddle here