Outer join criteria

[Dave]

How do I show all the records in table A that is outer joined to table B with
a criteria in table B?

Thanks

Dave

 

[Duane Hookom]

You would usually add
Or Is Null
to the criteria.

 

[Allen Browne]

Build a query into table B, with the criteria you want.

Then create a new query, using table A with an outer join to the query you
just created.

 

[Dave]

Not getting all the records in tbl A with tbl b criteria = "H" Or Is Null.
Tbl B has all the linked field records as tbl A but the criteria field is
populated with other items beside "H".

 

[bcap]

SELECT * FROM tableA LEFT JOIN tableB ON (tableA.something =
tableB.something AND tableB.something_else = "H")

Do NOT omit the brackets, without them it will throw an error.

 

[Michel Walsh]

Ah, I like that one. Long time ago (not so long, in fact), Jet was not
behaving in the same way than MS SQL Server, but it seems Microsoft did
manage to change Jet, after all, ... well, almost. In Northwind, try:

SELECT Employees.LastName
FROM Customers LEFT JOIN Employees
ON (Customers.CompanyName = Employees.LastName AND Employees.LastName =
"Tweety Bird")

Take a look at the result, many records are returned., all empty.
Clearly no one has his/her last name = "Tweety Bird", so the condition is
always false.
So, change the ON clause with something that will be always .... false...



SELECT Employees.LastName
FROM Customers LEFT JOIN Employees ON (Customers.CompanyName =
Employees.LastName AND false)


and this time, no record are returned.


And, if you ever run the first query on an unpatched Jet, you will also get
no record at all, with the first query.


So, be very careful using this syntax, with Jet. Microsoft seems to like to
change its behavior.




Vanderghast, Access MVP

 

[Duane Hookom]

If the other suggestions don't work, reply back with your SQL.