Can inner join be done on queries?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PatrickM - 26 Mar 2008 14:13 GMT

I'm running a query that joins two sub-queries. It doesn't work when I use
an inner join, but works when I use a left join. The SQL is:

SELECT [0106 treat srcedat gwsw 3].*
FROM [0106 treat srcedat gwsw 4] INNER JOIN [0106 treat srcedat gwsw 3] ON
[0106 treat srcedat gwsw 4].Works_number = [0106 treat srcedat gwsw
3].Works_number;

Some relevant points:
- Query 4 is on the left, query 3 is on the right.
- It is a one-to-many match with the 'one' being in 4 and the 'many' in 3.
- The join is on the field 'Works_number'.
- All works numbers in query 4 are in query 3.
- Query 4 is not a sub-query of query 3 and vice versa.

In theory the query should return the same results whether it uses an inner
join or a left join; however in practice the query returns no results if it
uses as an inner join and works fine if it uses a left join.

I think the reason it doesn't work as an inner join is that Access does not
know which of the two sub-queries (query 4 or query 3) to execute first. By
using a left join I give Access the 'directionality' or 'order' that it needs
for executing the sub-queries. Is my understanding correct?

Thanks,
Patrick

Reply to this Message

Allen Browne - 26 Mar 2008 17:02 GMT

An inner join returns results only where *both* the source tables have the
matching value.

You say:
All works numbers in query 4 are in query 3.
You did not say:
All works numbers in query 3 are in query 4.
Unless that is also true, the inner join should not return the records.

Therefore I did not follow your comments about directionality. There is no
directionality in the inner join, because the match must be found in both
sides.

Basic info:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm running a query that joins two sub-queries. It doesn't work when I
> use
[quoted text clipped - 28 lines]
> Thanks,
> Patrick

Reply to this Message

PatrickM - 26 Mar 2008 19:45 GMT

Thanks for your reply Allen.

Not all the works numbers in query 3 are in query 4; however the problem
with the inner join is that it returns no records at all whereas the left
join returns the records I would expect. If the inner join worked it would
return the same records as the left join since all the works numbers in query
4 are in query 3. There are many records in query 3 with works numbers that
match records in query 4.
There are no nulls in the works number field in queries 3 or 4.

That's why I'm wondering if the order of execution of queries 4 and 3 is
what is confusing Access. Any thoughts on this? I should have avoided the
use of the term 'directionality' in my initial posting since I was not
referring to the direction of the arrow that joins the two sub-queries in the
QBE grid. I really meant order of execution of the sub-queries. To clarify,
if sub-queries 3 and 4 were tables, I suspect the inner join would work just
fine. But because they are queries, Access must first execute them, but may
be confused by which to execute first when they are joined by an inner join.

Thanks,
Patrick

> An inner join returns results only where *both* the source tables have the
> matching value.
[quoted text clipped - 46 lines]
> > Thanks,
> > Patrick

Reply to this Message

Allen Browne - 27 Mar 2008 01:59 GMT

There's a couple of things that could be going on.

One possibility is that JET is not matching the values from queries 3 and 4
due to a difference in data type. Open the Immediate Window (Ctrl+G) and ask
Access to tell you the type of each field, e.g.:
? CurrentDb.QueryDefs("0106 treat srcedat gwsw
3").Fields("Works_number").Type

Are they the same type? The numbers will match one of the DAO decimal values
from here:
http://allenbrowne.com/ser-49.html
If they are text type (10), you might also look at the Len() of the fields
you expect to match. There may be a spurious character embedded in the
field, such as a space, tab, or nullchar.

A compact/repair would also be worthwhile, in case the problem is a bad
index.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for your reply Allen.
>
[quoted text clipped - 83 lines]
>> > Thanks,
>> > Patrick

Reply to this Message

PatrickM - 28 Mar 2008 15:21 GMT

Allen, doesn't the successful left join prove that this is not a data type
issue? Incidentally, sub-queries 3 and 4 extract data from the same table.
The works number I'm joining them on comes from the same field in the same
table.

I tried the compact and repair but the inner join still returns no records.

Thanks,
Patrick

> There's a couple of things that could be going on.
>
[quoted text clipped - 101 lines]
> >> > Thanks,
> >> > Patrick

Reply to this Message

Allen Browne - 28 Mar 2008 16:26 GMT

Patrick, there's nothing further I can suggest from the information you
supplied.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen, doesn't the successful left join prove that this is not a data type
> issue? Incidentally, sub-queries 3 and 4 extract data from the same
[quoted text clipped - 126 lines]
>> >> > needs
>> >> > for executing the sub-queries. Is my understanding correct?

Reply to this Message

PatrickM - 28 Mar 2008 16:46 GMT

Allen, no problem, I appreciate your taking the time to make several responses.

Patrick

> Patrick, there's nothing further I can suggest from the information you
> supplied.
[quoted text clipped - 129 lines]
> >> >> > needs
> >> >> > for executing the sub-queries. Is my understanding correct?

Reply to this Message

Jerry Whittle - 26 Mar 2008 17:14 GMT

Are you sure that there are matching records in [0106 treat srcedat gwsw 4]?

I'd do it this way:

SELECT [0106 treat srcedat gwsw 3].*
FROM [0106 treat srcedat gwsw 3]
WHERE [0106 treat srcedat gwsw 3].Works_number IN
(SELECT [0106 treat srcedat gwsw 4].Works_number
FROM [0106 treat srcedat gwsw 4]) ;

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I'm running a query that joins two sub-queries. It doesn't work when I use
> an inner join, but works when I use a left join. The SQL is:
[quoted text clipped - 22 lines]
> Thanks,
> Patrick

Reply to this Message

PatrickM - 26 Mar 2008 20:09 GMT

Thanks Jerry for you reply.

Yes, there are definitely matching works numbers in queries 4 and 3.

Thanks for your alternate SQL script, I like the insight.
I tend to build my queries using the QBE grid and avoid writing SQL script
as I don't have much SQL background and anyway find it easier and quicker to
let the QBE grid write the script for me. I copied your script into the SQL
view of the query and found that the QBE grid has a hard time dealing with
it, or might not be able to handle it at all. Do you know if the query (as
you scripted it in SQL) could be built in the QBE grid and how this would be
done? As far as I know the QBE grid has limitations in what SQL script it
can represent but I'd be happy to discover otherwise as I like using it.

Thanks,
Patrick

> Are you sure that there are matching records in [0106 treat srcedat gwsw 4]?
>
[quoted text clipped - 32 lines]
> > Thanks,
> > Patrick

Reply to this Message