Solved Filtering a many side of relation with two conditions. (1 Viewer)

deletedT

Guest
Local time
Today, 08:15
Joined
Feb 2, 2019
Messages
1,218
I've never been good in queries. So a little help is much appreciated. It's even difficult to explain what I need.
A sample database is attached if you need to see the situation.

This is the relationship between 4 tables in the sample database :

RelationShip.jpg


Imagine the database controls a production line.
  1. There are 11 possible processes that each order can go through (list of processes are in tblProecesses).
  2. A list of necessary processes for each product is registered in tblOrderProgress.
  3. When a process is done, tblOrderProgress will be updated : Which Order(OrderFK), which process(ProcessFK), who done it (UserFK) and when done(RegisteredOn).
  4. When a product is finished tblOrderProgress will be updated: Which Order(OrderFK), ProcessFK=11, who done it (UserFK) and when Delivered (RegisteredOn).
    Note: Delivery's ProcessFK is 11.
    Hence ProcessFK=11 AND RegisteredOn=Null means that order is not finished yet.
Now I need to write a query that shows a list of not finished orders where their ProcessFK 3 is not processed (ProcessedFK=3 AND RegisteredOn IS NULL).


In another word I need a list of orders that their records in tblOrdersProgress has the following two conditions:
  1. RegisteredOn for ProcessFK=3 is Null
  2. RegisteredOn for ProcessFK=11 is Null


I know I should explain it better, but with my level of English I couldn't manage to write it better.

I appreciate any kind of advice.
 

plog

Banishment Pending
Local time
Today, 03:15
Joined
May 11, 2011
Messages
11,611
know I should explain it better, but with my level of English I couldn't manage to write it better.

No clue you weren't a native speaker until that line.

Paste the below SQL into a new query object, run it, then if you want more fields go into design view and add whatever tables you need.

Code:
SELECT *
FROM tblOrderProgress
WHERE ((ProcessFK=3) OR (ProcessFK=11)) AND (RegisteredOn IS NULL)
 

strive4peace

AWF VIP
Local time
Today, 03:15
Joined
Apr 3, 2020
Messages
1,003
hi Tera,

try this SQL:

SELECT O.OrderPK FROM tblOrders as O WHERE
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=3 AND IsNull(RegisteredOn))
AND
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=11 AND IsNull(RegisteredOn))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:15
Joined
May 7, 2009
Messages
19,169
my guess is:

select tblOrders.* From tblOrders Where OrderPK Not IN
(select OrderFK From tblOrderProgress Where (ProcessFK = 11) Or (ProcessFK = 3 And Not (UserFK Is Null)))
 
Last edited:

deletedT

Guest
Local time
Today, 08:15
Joined
Feb 2, 2019
Messages
1,218
@strive4peace Million thanks.
I spent around half a day to write something like this, but never reached the goal.
Now I wish I had asked for help sooner instead of wasting my time.
Every time I ask for a query help and someone gives me the answer instantly, I promise to myself to start learning better, but it's always not enough.

I really appreciate your help.
 

deletedT

Guest
Local time
Today, 08:15
Joined
Feb 2, 2019
Messages
1,218
@arnelgp I appreciate your time. But unfortunately the result includes unwanted records too.

I appreciate for all the help you've been ever to me and the time you put on this question.
Thank you.
 

deletedT

Guest
Local time
Today, 08:15
Joined
Feb 2, 2019
Messages
1,218
Paste the below SQL into a new query object, run it, then if you want more fields go into design view and add whatever tables you need.

Code:
SELECT *
FROM tblOrderProgress
WHERE ((ProcessFK=3) OR (ProcessFK=11)) AND (RegisteredOn IS NULL)

@plog Unfortunately your codes shows one unwanted record.
But I appreciate your time and effort to help.

No clue you weren't a native speaker until that line.
I'm flattered. I'm from Japan, and after CoronaVirus crisis, if you happen to come here, I'll be your man. I know a lot of places where you can enjoy your time.
 

strive4peace

AWF VIP
Local time
Today, 03:15
Joined
Apr 3, 2020
Messages
1,003
dou itashimashite, Tera

> "I promise to myself to start learning better"

Let's break this statement down:
SELECT O.OrderPK FROM tblOrders as O WHERE
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=3 AND IsNull(RegisteredOn))
AND
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=11 AND IsNull(RegisteredOn))


This part is easy:
SELECT O.OrderPK FROM tblOrders as O

we are selecting the OrderPK from the tblOrders table. You can add other fields and join other tables in as needed.

as O
means the table will be abbreviated as "O" in the SQL Statement so referencing the table is quicker. "O" is called an alias.

WHERE
means there are conditions for selecting records

your first condition:
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=3 AND IsNull(RegisteredOn))

your second condition:
O.OrderPK IN (SELECT OrderFK FROM tblOrderProgress WHERE ProcessFK=11 AND IsNull(RegisteredOn))

(SELECT ...) is a subquery

O.OrderPK IN (SELECT OrderFK ...) means that OrderPK is in the list of OrderFKs returned by the subquery

AND
means that both conditions must be true

Subqueries are wonderful when the condition(s) you want to match come from another table. This method will be very helpful for your database!
 
Last edited:

Users who are viewing this thread

Top Bottom