0

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")

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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