Inner and outer joins in the same query

[Katsku]

[FONT='PrimaSans BT,Verdana,sans-serif']Hello,

I've been trying to work this out but have not been able to solve this. If anyone is able to help it would be greatle appreciated. The below query (simplified) works fine in MySQL but I'm not able to make it work in Access:

SELECT customer.customer_id, cust_category.category_name
FROM customer
LEFT JOIN
(
cust_category
INNER JOIN categories_of_customers ON categories_of_customers.category_id = cust_category.category_id
)
ON categories_of_customers.customer_id = customer.customer_id;

I'm trying to present all the customers and the name of their category. Some customers don't belong to any category but I would still like to include them in the result.

What would be the best way to do this in Access?


Many thanks!

[/FONT]

 

[namliam]

Try changing your inner join to match the join you have above it (left)

Doing an inner join to a left join doesnt make sense, therefor access will throw an error "ambigious joins" or something...
SELECT customer.customer_id, cust_category.category_name
FROM customer
LEFT JOIN
(
cust_category
LEFT JOIN categories_of_customers ON categories_of_customers.category_id = cust_category.category_id
)
ON categories_of_customers.customer_id = customer.customer_id;

 

[Katsku]

Hi,

Thanks for your help. However, Access does not accept this either, I get an error saying (the same one as previously) join not supported. I got it working by using:

[FONT='PrimaSans BT,Verdana,sans-serif']SELECT customer.customer_id, cust_category.cust_category_name
FROM
(
(customer) LEFT JOIN [/FONT][FONT='PrimaSans BT,Verdana,sans-serif']categories_of_customers ON categories_of_customers.category_id = cust_category.category_id
[/FONT][FONT='PrimaSans BT,Verdana,sans-serif'])
LEFT JOIN cust_category ON [/FONT][FONT='PrimaSans BT,Verdana,sans-serif']categories_of_customers.customer_id = customer.customer_id;

What I still have not been able to figure out is how to nest multiple queries in this manner. The issue is that I have a couple of junction tables I would need for the query.
In MySQL the whole query looked something like this:

[/FONT][FONT='PrimaSans BT,Verdana,sans-serif']SELECT customer.customer_id, cust_category.category_name, line_of_business.line_of_business
FROM customer
LEFT JOIN
(
cust_category
LEFT JOIN categories_of_customers ON categories_of_customers.category_id = cust_category.category_id
)
ON categories_of_customers.customer_id = customer.customer_id;[/FONT]
[FONT='PrimaSans BT,Verdana,sans-serif']LEFT JOIN
(
line_of_business
LEFT JOIN cust_line_of_business ON [/FONT][FONT='PrimaSans BT,Verdana,sans-serif']cust_line_of_business[/FONT][FONT='PrimaSans BT,Verdana,sans-serif'].line_of_business_id = [/FONT][FONT='PrimaSans BT,Verdana,sans-serif']line_of_business[/FONT][FONT='PrimaSans BT,Verdana,sans-serif'].line_of_business_id[/FONT]
[FONT='PrimaSans BT,Verdana,sans-serif'])
ON [/FONT][FONT='PrimaSans BT,Verdana,sans-serif']cust_line_of_business[/FONT][FONT='PrimaSans BT,Verdana,sans-serif'].customer_id = customer.customer_id[/FONT];

As you probably see, I'm quite new with SQL and Access, so any advice you may have is greatly appreciated.

Many thanks.

 

[Atomic Shrimp]

Is it possible to perform the two different joins in separate queries, then use a third query to bring them together?

 

[namliam]

Nested joins like this are a mess....
Try doing it with the "proper" syntax:
Select
from table1
Join Table2 on ...
Join Table3 on ....
Join Table4 on .....
Etc...

Plus you seem to have a surplus ; in there, there should only be one ; in there as the very last character.
I think what you are looking for is something like:

SELECT customer.customer_id
,      cust_category.category_name
,      line_of_business.line_of_business
FROM      customer
LEFT JOIN categories_of_customers   ON customer.customer_id                 = categories_of_customers.customer_id
LEFT JOIN cust_category             ON categories_of_customers.category_id  = cust_category.category_id

LEFT JOIN line_of_business          ON customer.customer_id                 = cust_line_of_business.customer_id 
LEFT JOIN cust_line_of_business     ON line_of_business.line_of_business_id = cust_line_of_business.line_of_business_id
;

This should work, but this:

SELECT customer.customer_id
,      cust_category.category_name
,      line_of_business.line_of_business
FROM      customer
LEFT JOIN categories_of_customers   ON customer.customer_id                 = categories_of_customers.customer_id
LEFT JOIN cust_category             ON categories_of_customers.category_id  = cust_category.category_id

LEFT JOIN line_of_business          ON customer.customer_id                 = cust_line_of_business.customer_id 
INNER JOIN cust_line_of_business     ON line_of_business.line_of_business_id = cust_line_of_business.line_of_business_id
;

Wont

 

[Katsku]

Thank you so much for helping the newbie out!! It works perfectly. Saved my weekend!