Hi, I've written a report to search an Inventory table using a list of Locations.
The report will output a list of locations with Inventory quantities.
But the problem I have is that if one of the Locations doesn't have any Inventory, then this Location isn't printed on the report.
I know that is what's meant to happen since the SELECT function will only display rows where a match is found, but is there anyway I can display the search criteria (i.e. the locations) even if there is no corresponding value in the Inventory table?

Thanks.
Jaz

What reporting tool are you using?

-PatP

one word: LEFT OUTER JOIN

I'm not using a reporting tool, just SQL written report.

r937: what do you mean by left outer join? sorry for my ignorance.

consider

Pets
1 dog
2 cat
3 bird
4 ferret

People
35 curly
38 larry
39 moe

PeoplePets
35 2
35 3
39 1


list all people with pets (INNER join) --

curly cat
curly bird
moe dog

list all people, and their pets if any (LEFT OUTER join) --

curly cat
curly bird
larry NULL
moe dog

list all people and a count of their pets (LEFT OUTER join) --

curly 2
larry 0
moe 1

Hi r937, thanks for your help. I've managed to get it working now.

Much appreciated !!