Can two queries be re-written into a single query? (1 Viewer)

[lithium1976]

Hello,

I was just wondering if the following two queries can be re-written into one single query or is this not do-able?

They are based off a many-to-many relationship between tblPeople and tblJobs with a link table called tblLinkPeoplesJobs in the middle.

I am pretty much an SQL novice, so am asking purely out of interest.

Thank you

SELECT tblPeople.peopleID, tblPeople.peopleFirstName, tblPeople.peopleSurname, tblLinkPeoplesJobs.jobID

FROM tblPeopleINNER JOIN tblLinkPeoplesJobsON tblPeople.peopleID= tblLinkPeoplesJobs.peopleID

WHERE (((tblLinkPeoplesJobs.jobID)=[forms]![frmSearch]![listPeople]));

and

SELECT tblPeople.peopleID, Query1.jobID, tblPeople.peopleFirstName, tblPeople.peopleSurname

FROM Query1 RIGHT JOIN tblPeopleON Query1.peopleID= tblPeople.peopleID

WHERE (((Query1.jobID) Is Null));

 

[Pat Hartman]

No. The tables are different and the criteria is different.

Are you asking if the recordsets can be combined? That is a different question. In that case, you could use a Union query. HOWEVER, you need to clean up the two queries you posted so that the field orders are IDENTICAL.

Also, you have typos in both queries. It is always better to copy and paste than to retype.

 

[The_Doc_Man]

In general when you are exploiting a many-many JOIN through a junction table, you write the SELECT clause for all the fields you want to see together - i.e. SELECT A.something, B.anothersomething, C.somethingelse... FROM ...

The FROM clause is written in parts: FROM ( ( A INNER JOIN B ON A.AID = B.BID ) INNER JOIN C ON A.XID = C.CID ) and note that the parentheses, while not strictly necessary, help you to see the method of the JOIN.

Also NOTE CAREFULLY that while you CAN do this, sometimes Access makes you physically split the query because of the "direction" of the relationship arrows. What I mean by this is that sometimes, the one/many arrows leading to the item point into the center, which can confuse the query builder. In that case you have to break it up into two queries.

 

[isladogs]

This article may be helpful:

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

 

[CJ_London]

I assume the first sql is the query1 referred to in the second sql. In which case you can combine them as

SELECT tblPeople.peopleID, Query1.jobID, tblPeople.peopleFirstName, tblPeople.peopleSurname

FROM (SELECT tblPeople.peopleID, tblPeople.peopleFirstName, tblPeople.peopleSurname, tblLinkPeoplesJobs.jobID
            FROM tblPeople INNER JOIN tblLinkPeoplesJobsON tblPeople.peopleID= tblLinkPeoplesJobs.peopleID
            WHERE (((tblLinkPeoplesJobs.jobID)=[forms]![frmSearch]![listPeople]))) as Query1

RIGHT JOIN tblPeople ON Query1.peopleID= tblPeople.peopleID

WHERE (((Query1.jobID) Is Null));

But not clear why you are returning so many fields from query1 when all you want is peopleID and jobID

 

[Pat Hartman]

Clearly none of us has any idea what your objective might be. Perhaps three recordsets will solve the problem so we can tell how you are trying to combine the data.

query1
query2
results

 

[Isaac]

@lithium1976

Did you know that you can simply edit one of the queries to refer to the second query as if it were a Table - as long as there is a column that's appropriate for Joining?

 

[Pat Hartman]

OK, now we have four people assuming a join is what the OP requested and me assuming he needed a union. Maybe someday, we'll know

 

[Isaac]

OK, now we have four people assuming a join is what the OP requested and me assuming he needed a union. Maybe someday, we'll know

good point ... who knows.
honestly if someone is on Day 1 of writing queries and I see a Right Join I highly doubt that even what they already wrote is correct or they have any idea what they are doing or why.

I think they were just playing with the options on the GUI.

 

[The_Doc_Man]

I based my answer on this statement from the OP's post #1:

"They are based off a many-to-many relationship between tblPeople and tblJobs with a link table called tblLinkPeoplesJobs in the middle."

 

[KitaYama]

That's why I love this forum. The OP asks something and never show back again, but the members are still debating and insisting to help.

 

[ebs17]

It has been a long time ...

two queries can be re-written into one single query or is this not do-able?
They are based off a many-to-many relationship between tblPeople and tblJobs with a link table called tblLinkPeoplesJobs in the middle.

I assume with some confidence that the first query is the one used as Query1 in the second query. Then putting them together is extremely easy, just copy and paste.

SELECT tblPeople.peopleID, Query1.jobID, tblPeople.peopleFirstName, tblPeople.peopleSurname
FROM

(
SELECT tblPeople.peopleID, tblPeople.peopleFirstName, tblPeople.peopleSurname, tblLinkPeoplesJobs.jobID
FROM tblPeopleINNER JOIN tblLinkPeoplesJobsON tblPeople.peopleID= tblLinkPeoplesJobs.peopleID
WHERE (((tblLinkPeoplesJobs.jobID)=[forms]![frmSearch]![listPeople]))
) AS Query1

RIGHT JOIN tblPeopleON Query1.peopleID= tblPeople.peopleID
WHERE (((Query1.jobID) Is Null))

Whether the overall design makes sense is another question.

You would certainly have to clean up a lot, but you can see the problems at a glance (thanks to the composition):

- The many brackets only confuse and should be removed as much as possible.

- The subquery returns many fields, the main query uses only one of them. swipe.

- The form reference in the subquery should cause problems. But there are parameter queries, and parameters can also be set at lower query levels.

- The criteria in the subquery and main query contradict each other (jobID=37 versus jobID IS NULL).

- The double use of tblPeople is certainly reworkable.

Before a final revision, I would also ask again what the actual goal of the overall query is: A question of meaning in addition to the question of technology.