urgent ! outer join not picking all records

[Guest]

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.Field2, b.Field3, c.Field1,
c.Field2, c.Field3
FROM (query2 AS a LEFT JOIN query2 AS b ON a.Field1 = b.Field1) LEFT JOIN
query2 AS c ON a.Field1 = c.Field1
WHERE (((a.rank)=1) AND ((b.rank)=2) AND ((c.rank)=3));

thats the code and because of "where" clause only records that match all the
criteria are returning THOUGH its is an outer join ! I would like all records
from query "a" where rank = 1 even if corresponding b and c records don't
exist.
Please tell how is it possible.???

 

[John Vinson]

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.Field2, b.Field3, c.Field1,
c.Field2, c.Field3
FROM (query2 AS a LEFT JOIN query2 AS b ON a.Field1 = b.Field1) LEFT JOIN
query2 AS c ON a.Field1 = c.Field1
WHERE (((a.rank)=1) AND ((b.rank)=2) AND ((c.rank)=3));

thats the code and because of "where" clause only records that match all the
criteria are returning THOUGH its is an outer join ! I would like all records
from query "a" where rank = 1 even if corresponding b and c records don't
exist.
Please tell how is it possible.???

If there is no b record, then b.rank is certainly not equal to 2; and
if there is no c record, then c.rank is certainly not equal to 3!
You're getting all the qualified records because you're insisting on
choosing only existing records in b and c with specific values.

Try

WHERE (((a.rank)=1) AND ((b.rank)=2 OR (b.rank) IS NULL) AND
((c.rank)=3) or (c.rank) IS NULL);


John W. Vinson[MVP]

 

[Guest]

It did not help, i still get only the records that match the criteria. I
would think by using an outer join i still should get all the records from
"a" through the b and c don't match the criteria ???

 

[John Viescas]

When you add a filter to the "right" table in a Left Join, you get the
equivalent of an Inner Join. You must apply the filter first, then do the
join. Like this:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.Field2, b.Field3, c.Field1,
c.Field2, c.Field3
FROM (query2 AS a
LEFT JOIN (SELECT * FROM query2 WHERE rank = 2) AS b
ON a.Field1 = b.Field1)
LEFT JOIN (SELECT * FROM query2 WHERE rank = 3) AS c
ON a.Field1 = c.Field1
WHERE ((a.rank)=1);

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[John Vinson]

When you add a filter to the "right" table in a Left Join, you get the
equivalent of an Inner Join.

Thanks John, and my apologies for posting in haste, Questy.

John W. Vinson[MVP]

 

[Guest]

Thanks for both of your time and help, i have it working now.

 

[Guest]

I'm surprised that the MVP didn't refer you to article # 275058 in the MS
Support knowledge base. You are correct in thinking that the outer join
should work even with filter criteria specified in the right-hand table. The
article above states that this is a bug in MSJet, which is supposedly fixed
since Jet SP4.

Nevertheless, I've installed SP8 and this query still doesn't work.

Danny

 

[Van T. Dinh]

This article only applies to "SQL Server Compatible Syntax (ANSI 92)" option
if you read the "Cause" section of the article properly.

Did you select this option in your database?

If you haven't, you are using JET syntax which is different.

Since the O.P. didn't mention the "ANSI-92" option, John Vinson (and others)
would normally assume JET syntax and his reply is correct and applicable to
the O.P.'s question except I would have used different positioning of the
parentheses like:

.....
WHERE (
( (a.rank) =
)
AND ( ( (b.rank) = 2 ) OR ( (b.rank) IS NULL ) )
AND ( ( (c.rank) = 3 ) OR ( (c.rank) IS NULL ) )
);

--
HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Don't know what happened with the post but the WHERE String I meant was:

.....
WHERE (
( (a.rank) = 1 )
AND ( ( (b.rank) = 2 ) OR ( (b.rank) IS NULL ) )
AND ( ( (c.rank) = 3 ) OR ( (c.rank) IS NULL ) )
);

 

[Guest]

No, I didn't select that option. I didn't understand what that meant. Oops.

I did just now change that option for the database I'm working with, and the
results are the same - I'm still not getting true outer join results.

 

[Van T. Dinh]

Try the JET SQL syntax as per John Vinson's suggestion.

Post your SQL String also.