If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
urgent ! outer join not picking all records
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.??? |
#2
|
|||
|
|||
On Thu, 24 Feb 2005 16:29:03 -0800, questy
wrote: 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] |
#3
|
|||
|
|||
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 Vinson" wrote: On Thu, 24 Feb 2005 16:29:03 -0800, questy wrote: 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] |
#4
|
|||
|
|||
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) "questy" wrote in message ... 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.??? |
#5
|
|||
|
|||
On Thu, 24 Feb 2005 22:11:48 -0600, "John Viescas"
wrote: 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] |
#6
|
|||
|
|||
Thanks for both of your time and help, i have it working now.
"John Vinson" wrote: On Thu, 24 Feb 2005 22:11:48 -0600, "John Viescas" wrote: 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] |
#7
|
|||
|
|||
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 "questy" wrote: 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 Vinson" wrote: On Thu, 24 Feb 2005 16:29:03 -0800, questy wrote: 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] |
#8
|
|||
|
|||
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) "Danny" wrote in message ... 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 "questy" wrote: 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 Vinson" wrote: On Thu, 24 Feb 2005 16:29:03 -0800, questy wrote: 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] |
#9
|
|||
|
|||
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 ) ) ); -- HTH Van T. Dinh MVP (Access) "Van T. Dinh" wrote in message ... 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) |
#10
|
|||
|
|||
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" wrote: 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) "Danny" wrote in message ... 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 "questy" wrote: 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 Vinson" wrote: On Thu, 24 Feb 2005 16:29:03 -0800, questy wrote: 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] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
urgent ! Delete Dupe records and also Append Fields | vbastarter | Running & Setting Up Queries | 4 | February 25th, 2005 01:49 AM |
Nested Select Outer Join | rjeffres | General Discussion | 2 | February 19th, 2005 09:07 PM |
not-equal, theta join | Nick Hoffman | Running & Setting Up Queries | 1 | February 10th, 2005 02:03 PM |
Calculated Value will not store in Table | tonyaims | Using Forms | 10 | September 14th, 2004 03:11 AM |