Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL join 3 tables & show all rows from one

Posted on 2013-02-07
8
Medium Priority
?
384 Views
Last Modified: 2013-02-15
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)??
0
Comment
Question by:SidFishes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Expert Comment

by:RustyZ32
ID: 38865660
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.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38865672
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
0
 
LVL 36

Author Comment

by:SidFishes
ID: 38865759
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Accepted Solution

by:
RustyZ32 earned 1500 total points
ID: 38865787
ok, I see what you mean, try this:



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

UNION

SELECT a_id, storename, NULL, NULL
FROM accounts t1
WHERE a_id NOT IN (SELECT a_fk FROM products_accounts)

ORDER BY  a_id
0
 
LVL 36

Author Comment

by:SidFishes
ID: 38866013
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..
0
 
LVL 39

Expert Comment

by:appari
ID: 38866648
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38866859
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

0
 
LVL 36

Author Comment

by:SidFishes
ID: 38895128
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.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question