Can inner join be done on queries?

[PatrickM]

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

 

[Allen Browne]

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

 

[Jerry Whittle]

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]) ;

 

[PatrickM]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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

 

[PatrickM]

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]?

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]) ;
--
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:

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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

 

[PatrickM]

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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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

 

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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.

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.

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.

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

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?

 

[PatrickM]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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
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.

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.

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.

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

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?