I'm stumped on this one. In short, I have 3 tables: Product, Ranging, Account. Ranging is the middle table which is the link between Product and Account.
e.g. a ranging record links to 1 account and 1 product. Products can have many rangings, so can Accounts.
What i'm trying to end up with is 3 columns: 1 = Product Name, 2 = Account Name, 3 = Ranging (exist (yes/no). That's easy, but the tricky bit is that I want to show EVERY Account under each product.
i.e. if I have PROD 1, 2 and 3 and account A, B and C. I want to see:
The account, product and ranging all have ID's that are linked (PK, FK etc)
I've tried a cross join with a inner join to no avail. Appreciate the help!
Are you looking for this?
SELECT q.product_name, q.account_name,
CASE WHEN r.product_id IS NULL THEN 'no' ELSE 'yes' END status
FROM
(
SELECT product_id, product_name, account_id, account_name
FROM product p CROSS JOIN account a
) q LEFT JOIN ranging r
ON q.product_id = r.product_id
AND q.account_id = r.account_id
ORDER BY q.product_name, q.account_name
Sample output:
| PRODUCT_NAME | ACCOUNT_NAME | STATUS | ---------------------------------------- | Product 1 | Account A | yes | | Product 1 | Account B | no | | Product 1 | Account C | yes | | Product 2 | Account A | yes | | Product 2 | Account B | no | | Product 2 | Account C | no | | Product 3 | Account A | no | | Product 3 | Account B | no | | Product 3 | Account C | no |
Here is SQLFiddle demo