In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!
Solved
SQL join 3 tables & show all rows from one
Posted on 2013-02-07
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)??
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)??
-
3
2
- +2
8 Comments
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.
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.
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
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
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.
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.
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
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
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..
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..
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
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
Active today
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
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
Featured Post
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple. The final solution for this task involved creating a custom text parser and then reading…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.
As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month4 days, 5 hours left to enroll
589 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.