RE: Can inner join be done on queries?

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

"Jerry Whittle" wrote:

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.

"PatrickM" wrote:

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


.

Relevant Pages

  • Re: criteria problem
    ... You can do it in the query grid with a little mucking about. ... switch to SQL view and copy the SQL ... and paste your SQL script in between the braces. ...
    (comp.databases.ms-access)
  • Re: criteria problem
    ... You can do it in the query grid with a little mucking about. ... switch to SQL view and copy the SQL ... and paste your SQL script in between the braces. ...
    (comp.databases.ms-access)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)