MS Query and SQL - getting records when there is no supplier

Nov 29, 2006 3 Replies
J

I am trying to write a query where the following columns are present:



Item.Description Item.Price Department.Name Supplier.Name



The problem is that when I set up the joins in MS Query, the records that don't have an associated supplier assigned are not returned.



When I try to change the join type on Item.SupplieID-Supplier.ID, it says outer joins are not allowed with more than two tables in the query.



Any ideas on how I can get it to return all records even if there is no supplier?



Thanks,



Jason


Vote
Twitter Facebook LinkedIn
I

Hello Jason,

Use the following SQL statement:

select item.description, item.price, supplier.suppliername, department.name from item left join supplier on item.supplierid=supplier.id left join department on item.departmentid = department.id

Please tell me if this is what you are searching for.

Irina Stanca

"Jas> I am trying to write a query where the following columns are present: >

Vote
Twitter Facebook LinkedIn
G

I'm not familiar with MSQuery, but if it will let you insert the SQL command directly, it would be:

select item.description, item.price, department.Name, supplier.Name from item left join department on item.departmentid = department.id left join supplier on item.supplierid = supplier.id

Glenn Adams Tiber Creek C> I am trying to write a query where the following columns are present: >

Vote
Twitter Facebook LinkedIn
J

Glenn,

Kiss yourself for me. Worked perfectly.

"Glenn Adams [MVP - Retail Mgmt]" wrote in message news:O4kllm% snipped-for-privacy@TK2MSFTNGP06.phx.gbl...

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required