Using query results as a where condition? (1 Viewer)

reggiete

Registered User.
Local time
Today, 01:44
Joined
Nov 28, 2015
Messages
56
I have a select query that pull records based on certain where criteria. I want the recordIDs that are returned to be used in another query to see if the ID exists?

For example Select Query 1 pulls all records with a open status.
I want to have another query run taking the IDs found open in Query 1 to see if that ID exists with a closed status
 

isladogs

MVP / VIP
Local time
Today, 08:44
Joined
Jan 14, 2017
Messages
18,186
The way you describe it the second query will have no records. Or am I being dense?
 

plog

Banishment Pending
Local time
Today, 03:44
Joined
May 11, 2011
Messages
11,611
JOINS are essentially criteria:

Code:
SELECT tblClosed.ID
FROM tblClosed
INNER JOIN qryOpen ON qryOpen.ID = TableClosed.ID

With that said, if the open and closed records are coming from the same table, then it might be able to handle this with an aggregate query:

Code:
SELECT ID
FROM Table
WHERE Status='Open' OR Status='Closed'
GROUP BY ID
HAVING MAX(Status) <> MIN(Status)
 

reggiete

Registered User.
Local time
Today, 01:44
Joined
Nov 28, 2015
Messages
56
ok Plog. I create to temp tables.

T1 has all the IDs with open ( Query 1 inserted into)
and T2 has all the IDs with closed ( query 2 Inserted into)

Now I want to join the two tables and give me a list of IDs from T1 where the ID was not found in T2

Create Table #T1 (
TicketHistoryID varchar(255), TicketID varchar(255), MonthYear varchar(255), ActionTaken varchar(255), Status varchar(255), Mailbox varchar(255), TicketBucket varchar(255), DateTime DateTime)
Create Table #T2 (
TicketHistoryID varchar(255), TicketID varchar(255), MonthYear varchar(255), ActionTaken varchar(255), Status varchar(255), Mailbox varchar(255), TicketBucket varchar(255), DateTime DateTime)
 

plog

Banishment Pending
Local time
Today, 03:44
Joined
May 11, 2011
Messages
11,611
ok Plog. I create to temp tables.

Why? No reason to create temp tables, this can all be done via SQL. Also, you have changed what you want:

Now I want to join the two tables and give me a list of IDs from T1 where the ID was not found in T2

That is entirely different from your initial post. Its still doable via a simple query, but this is not the same as what you initially posted. For this you would use a LEFT JOIN:

Code:
SELECT ID 
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID
WHERE Table2.ID IS NULL
 

HiTechCoach

Well-known member
Local time
Today, 03:44
Joined
Mar 6, 2006
Messages
4,357
I agree with plog that this can all be done with just queries (SQL).

TIP: When you are in the query designer, you can add a (saved) select query and use it just like you would a table!


You can even use the Unmatched query wizard to create this by selecting a table and the saved query.

Anther option is to use a sub query.
 

Users who are viewing this thread

Top Bottom