JOIN Conditions becoming Where Conditions

[nicklaspet]

I have found that the folowing query gives the wrong result in number of hits, because its a LEFT JOIN it should return all rows from Table1 but does only return a subset, it seems that the Access engine makes the second condition in the Join condition a where condition instead of a condition for the Join!


SELECT DISTINCT
A.ID,
B.ID

FROM Table1 AS A

LEFT JOIN Table2 AS B ON (A.ID=B.IDX And B.Number=9)



On other SQL engines ad for example(MSSql 6.5, 7.0, and 2000) the Join condition is used correctly.

I have found another way to write the query so that it returns the correct result, works find with about 10 rows in the table but it takes about 40 minutes to return 5000 rows so I dont think I want to use that way, though here it is if it helps..

SELECT DISTINCT
A.ID,
B.ID

FROM Table1 AS A

LEFT JOIN Table2 AS B ON (1 = 0) OR (A.ID=B.IDX And B.Number=9)


Doesnt quite understand whats happening inside the Access engine but probably the second query joins all tables from the Table2 to each row into Table1 and then filters among them, that would explain the time factor


Is there any other way to write the query so that is will work, with good performance ?

 

[Tim1]

Hiya,

I am not quite sure what your desire result is, but you could try putting the following into the Access SQL screen:

SELECT DISTINCT A.ID,
B.ID
FROM Table1 A,
Table2 B
WHERE A.ID = B.IDX
AND B.Number = 9;

That should return your desired result, I think....

Tim

 

[nicklaspet]

No, thats the old JOIN standard, have tried that, it doesnt work eighter, its an INNER JOIN, and it seems that Access doesnt accept the use of *= instead, that should make it a LEFT JOIN, support for the older standard SQL queries seem to be on its way out, thanks anyway Tim.

SELECT DISTINCT A.ID,
B.ID
FROM Table1 A,
Table2 B
WHERE A.ID *= B.IDX
AND B.Number = 9;

 

[FinnB]

Hi,
I suggest this sql:
SELECT DISTINCT
A.ID,
B.ID

FROM Table1 AS A

LEFT JOIN Table2 AS B ON (A.ID=B.IDX )
where
B.Number=9
----
Best regards,
FinnB

 

[SarsonWilson]

I feel your pain, and unfortunately I haven't found the answer anywhere. The left join acts like an inner join when you have criteria in the right table, and that's just not what we want! If you do find an elegant solution, could you please post here? I found a round about way using a succession of several queries and "temporary" tables, but I'd like to know how to do it in one query. Thanks!

 

[SarsonWilson]

if it helps other people, here is what I want:
I have 2 tables, "Author" and "Book"

the contents of the Author table:
SSN FirstName LastName
111111111 Adam Wilson
222222222 Emily Wilson
333333333 Arthur Jones
444444444 Syed Karimushan
555555555 Alexandra Wilson

the contents of the Book table:
SSN Title Year
111111111 Thief of Time 2000
333333333 Wyrd Sisters 1999
555555555 Guards, Guards! 1998

and using the following criteria:
ALL entries in "Author" where LastName = "Wilson"
ONLY entries from &quot;Book&quot; where PublishingDate < &quot;2000&quot;

I want the following result:
SSN FirstName Title Year
111111111 Adam NULL NULL
222222222 Emily NULL NULL
555555555 Alexandra Guards, Guards! 1998

in SQL server, this query is:
select A.SSN, A.FirstName, B.BookTitle, B.PublishingDate
from Author A, Book B
where
A.LastName = 'Wilson'
and A.SSN *= B.SSN
and B.PublishingDate < '2000'

which works perfectly, however the &quot;*=&quot; syntax in the where clause isn't supported in Access. When I use &quot;design view&quot; in Access, it creates the following SQL code:
SELECT Author.SSN, Author.FirstName, Book.BookTitle, Book.PublishingDate
FROM Author LEFT JOIN Book ON Author.SSN = Book.SSN
WHERE (((Author.LastName)='Wilson') AND ((Book.PublishingDate)<'2000'));

which gives the following result:
SSN FirstName Title Year
555555555 Alexandra Guards, Guards! 1998


which effectively does the same thing as an inner join, which is not what I want.

 

[Quehay]

Why not help the processing by using explicit subqueries.

Q1: Select DISTINCT A,B from table1


Q2: Select A,B from Q1 Left Join table2 t
where Q1.A = t.A and
Q1.B = t.B

The join is not the ANSI 92 standard that Access uses (it's more like Oracle ANSI 89) but it's understood by Access, and I find it more legible in a lot of cases.

I hope this addresses your problem correctly!

 

[cmmrfrds]

Have you tried adding a check for nulls as an OR condition.

((Book.PublishingDate)<'2000' OR (Book.PublishingDate) IS NULL));

 

[dalchri]

Quehay, thats on the track to what I was going to suggest... except I don't think that SarsonWilson wants to actually join on field B, just filter.

SarsonWilson, I suggest using subqueries to prefilter your tables before joining them if they are truly huge. Filtering is one of the least expensive operations in Access whereas joining is one of the most expensive.

For example:
[tt]
SELECT Author.SSN, FirstName, Title, [Year]
FROM (
SELECT SSN, FirstName
FROM A
WHERE LastName='Wilson'
) AS Author LEFT JOIN (
SELECT SSN, BookTitle AS Title, PublishingDate AS [Year]
FROM B
WHERE PublishingDate<'2000'
) ON Author.SSN=Book.SSN
[/tt]
I'm not sure I got all the field names right but it illustrates the concept. The results of the subqueries should contain far less records than the parent tables. This way, Access will spend most of its time filtering and far less time joining.

FYI, I bracketted the field name &quot;Year&quot; because I think it is a reserved word in Access SQL.

 

[SarsonWilson]

Thank you Quehay and dalchri. I appreciate the time to look at my problem.

I have found several multi-query solutions, although dalchri's is a new one to me. I don't fully understand why it does work, but I'll be studying it more to find out. Thankfully, time was not an issue on this (only a few thousand records) but I will keep the tip around.

 

[emilybartholomew]

I am trying to do this exact thing too, with the query as the record source for an Access form (A2000, with mysql db) I tried the query strategy offered by dalchri and it works like a charm - as a query. As the record source it produces a Microsoft Jet Database engine problem (the engine cna't find the query or table). Any suggestions?

 

[emilybartholomew]

OK, so I really should have tried a few things before I posted. Making the &quot;inner&quot; query into a saved query, and incorporating it into the bigger query by that name is what works.

 

[tlbroadbent]

Obviously there are numerous ways to write a query that will work. If you want one simple query that works, then use the solution suggested by cmmrfrds.

Select <column list>
From table1 a
Left Join table2 b
on a.Id=b.Idx
Where (b.number=9 Or b.Idx is null)

I don't know if this qualifies as elegant but it is simple. Adding the criteria b.Idx Is Null handles the condition where there is no matching row in table2. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[SarsonWilson]

Thank you for the reply tlbroadbent!
Unfortunately it brings back too much. There is criteria for the table1 (&quot;Author&quot. Adding this criteria (&quot;LastName = 'Wilson'&quot then makes the query:

SELECT a.FirstName, a.LastName, b.BookTitle, b.Year
FROM Author AS a
LEFT JOIN Book AS b ON
a.SSN = b.SSN
WHERE (a.LastName=&quot;Wilson&quot; AND b.PublishingDate < &quot;2000&quot
OR b.SSN Is Null


which brought back the Author &quot;Syed Karimushan&quot;. However, I only want authors with the last name &quot;Wilson&quot;. I also tried:

SELECT a.FirstName, a.LastName, b.BookTitle, b.Year
FROM Author AS a
LEFT JOIN Book AS b ON
a.SSN = b.SSN
WHERE (a.LastName=&quot;Wilson&quot; AND b.PublishingDate < &quot;2000&quot
OR (a.LastName=&quot;Wilson&quot; AND b.SSN Is Null)

but this brings back the same result that I originally had problems with.

Thank you for the alternate viewpoint though!

 

[tlbroadbent]

SarsonWilson,

I wasn't replying directly to your question but rather to the general discussion. I would write the Access query to return the results you seek in the following manner.

SELECT a.FirstName, q.Title, q.[Year]
FROM Author As a
LEFT JOIN
(Select SSN, Title, Year
From Book Where Year<2000) As q
ON a.SSN = q.SSN
WHERE a.LastName=&quot;Wilson&quot;;
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.