Avatar of SidFishes
SidFishes
Flag for Canada asked on

SQL join 3 tables & show all rows from one

Using mysql 5.5 but don't think that matters...

I have 3 tables

accounts
|a_id|StoreName|Address etc

products_accounts
|pa_id|p_fk|a_fk|

products
|p_id|UPC|ProdName| etc

some accounts have multiple products associated with them, some don't have any.

I need a query to return all accounts with associated products

result
----------------

a_id|StoreName | UPC | ProdName

1234 | Bob's Mart |12345 | WidgetA |
1235 | Bob's Mart |22345 | WidgetB |
1256 | Smart Mart |12345 | WidgetA |
3321 | Kwik Mart | null | null

I've tried a few combinations of left & right joins but can't seem to get it right.

I'm doing this in coldfusion so I can nest the product query within a store loop but Is this do-able with joins (or unions)??
MySQL ServerDatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
SidFishes

8/22/2022 - Mon
RustyZ32

SELECT t1.a_id, t1.storename, t3.upc, t3.prodname
FROM accounts t1
INNER JOIN products_accounts t2
ON t1.a_id = t2.a_fk
LEFT JOIN products t3
ON t2.p_fk = t3.p_id

 unless I am missing something about your tables that should return what you want.
Aaron Shilo

select allaccounts
from accounts left join products  
ON t1.id = t2.id -- this will return all accounts , if you need
                          --you can more tables using the left join all the way.
union
select allaccounts
from accounts  join products ON t1.id = t2.id -- this will return accounts with products
SidFishes

ASKER
Rusty, I basically had what you've posted, and I now realize (I think)  the reason it isn't working is that only accounts that have products associated with them are even -in- products_accounts table so the join can't work (as I was hoping)

I get products by accounts but if the account has no products associated with it, (ie: not in products_accounts table) then they don't show.

I think the only way to do this is in 2 parts. Unless someone has any other ideas.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
RustyZ32

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SidFishes

ASKER
OK that's doing the trick...brutally slow however (28secs to return 15k rows). I guess that's the problem with Union?

If I was just returning 1 row it would be ok but I'm doing proximity lookups based on lat/lon and can have up to 50 store locations in an area.

Going to have to try something else I think. Unless you have any other suggestions, I'll award pts & close the q..
appari

try this

Select a_id, StoreName ,  UPC ,  ProdName
from  accounts Left outer join products_accounts
on a_id =  a_fk
Left outer join products on p_id = p_fk
PortletPaul

with table references included :-)
SELECT accounts.a_id, accounts.StoreName , products.UPC , products.ProdName
FROM accounts
LEFT OUTER JOIN products_accounts ON accounts.a_id = products_accounts.a_fk
LEFT OUTER JOIN products          ON products_accounts.p_fk = products.p_id

Open in new window

and, if there is potential for repeated account-product pairs then maybe:
SELECT accounts.a_id, accounts.StoreName , products.UPC , products.ProdName
FROM accounts
LEFT OUTER JOIN (
                   SELECT DISTINCT
                   products_accounts.a_fk, products_accounts.p_fk
                   FROM products_accounts
                ) ap              ON accounts.a_id = ap.a_fk
LEFT OUTER JOIN products          ON ap.p_fk = products.p_id

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SidFishes

ASKER
apologies...ended up using CF for the solution and forgot this was open.

Paul & appari - the left outer joins work, but they are even slower than the UNION (38 vs 28 seconds)

Pts to Rusty for first solution even though it's not practical in this instance due to the query time issue.