mySQL query, should I use CASE, UNION or something else

Creating a query in rails, I'm pretty sure active records won't support a query of this type.

Have four table开发者_C百科s ( products, companies, stores, and product_inventories )

There is a Product_Inventory table that contains product_id, company_id, store_id. A company may or may not have many stores (Best Buy would have many stores while Amazon would have 1). If a row contains only a product and company all stores carry the product, but if it contains a store it only applies to that single location.

Product -> Product_Inventory -> Companies -> Stores
OR
Product -> Product_Inventory ->  Stores

GOAL: I want to be able to query a product and find all the locations where it is carried.

What I have:

    SELECT DISTINCT s.*, c.* FROM product_inventory p_inv 
    INNER JOIN stores s ON 
    ( p_inv.store_id = s.id                #SKIP when p_inv.store_id IS NULL
    OR 
    p_inv.company_id = s.company_id )      #SKIP when p_inv.store_id IS NOT NULL
    INNER JOIN companies c ON s.company_id = c.id
    WHERE p_inv.product_id = 1

---

So, your tables basically look like this:

Table pr_inv
p_id, c_id, s_id
 1     1     NULL
 2     1     1
 3     1     2
 4     2     NULL

Table products
pid
 1
 2
 3
 4
Table companies
cid
 1
 2

Table stores
sid
 1
 2

Last, you want to get all stores owned by a company. (I'm working backwards).

First, you want to get all companies that have a product.

SELECT DISTINCT c.* FROM (
    SELECT * FROM pr_inv WHERE p_id = 1
) as prod_id
INNER JOIN
companies as c
ON prod_id.c_id = c.cid

This returns all the companies that have that product. That's the part you already have.

SELECT DISTINCT s.* FROM (
    SELECT * FROM pr_inv WHERE p_id = 1
) as prod_id
INNER JOIN
stores as s
ON prod_id.s_id = s.sid

That gets all the stores you're interested in, except where stores are null.

SELECT s.* FROM (
   SELECT * FROM pr_inv WHERE p_id = 1 AND stores = null
) as prod_id
INNER JOIN company as c ON pr_inv.c_id = c.cid
LEFT JOIN stores as s ON s.cid = c.cid

That gets all the stores you're interested in where stores are null. Finally, you can hook the queries together using JOIN.