outer join with multiple fields

[Guest]

I've noticed an oddity when trying to perform an outer join using multiple
fields. If I create a Left Join between Table1 and Table2 using 2 fields
from both tables I get the correct results i.e. all records from Table1 and
only records that match (based on the 2 field join criteria) from Table2. If
instead of using Table2, I use a saved query that has the exact same
data/structure as Table2, the results are incorrect i.e. only the records
that match in both tables are returned. One thing to note that I think is
causing the problem is that the saved query uses a set field i.e. 'New York'
AS Location, which is one of the fields used in the outer join. Does anyone
know why the query version does not perform correctly?

Here are more details to my problem.
I have 2 tables. Table1 has the following fields: Date, Location, Quantity.
Table2 has the following fields: Date, Value. The data in Table2 applies
only to one Location, New York, which is also in certain records in Table1
i.e. Table1 has records pertaining to many different Locations. I want to
join the tables so the 'Value' field in Table2 is added to the New York
records in Table1 for the corresponding date. However, since Table2 doesn't
have a Location field that can be used in the outer join I created the
following saved query to accomodate this:

(SELECT
'New York' AS Location, Table2.Date, Table2.Value
FROM
Table2) AS Query2

I then use this saved query in my outer join query which is below.

SELECT
Table1.Date, Table1.Location, Table1.Quantity, Query2.Value
FROM
Table1 LEFT JOIN Table2 ON
(Table1.Date = Query2.Date) AND
(Table1.Location = Query2.Location);

The results that return from the above are incorrect. For some reason doing
it with the query seems to filter all of the rows in Table1 where Query2 is
null, thus performing a simple inner join. I believe it has something to do
with the first field in my saved query --> 'New York' AS Location, because it
is not from a table.

I've mocked this problem up with a Table2 that has a Location column with
'New York' in every row. Using this Table2 in the outer join query performs
correctly, which is what makes me believe the set Location field is to blame
somehow.

Thanks.

 

[Allen Browne]

The problem you describe sounds like this issue:
Records missed by SELECT query
at:
http://allenbrowne.com/bug-10.html

Microsoft knows about these flaws, but I cannot get them to fix them.

 

[Jamie Collins]

The problem you describe sounds like this issue:
Records missed by SELECT query

There's a full explanation of the issues here:

http://groups.google.com/group/comp.databases.ms-sqlserver/msg/0c20229366261b8d

Microsoft knows about these flaws

Indeed:

http://support.microsoft.com/?id=208880:

"this feature was implemented before the ANSI SQL-92 standard. Before
then, there was no outer join spec at all."

...but I cannot get them to fix them.

Me either <g>. Maybe the Access team will take up the challenge now
they have their 'private copy' of the Jet source code:

http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx

Jamie.

--