How to get absent FKs in a child table? (1 Viewer)

[deletedT]

I know it should be a simple task for most of you, but I'm failing to receive the results I need.
This is the relationship between 4 tables:

and this is the query :

I need this query show a list of parts that have no processFK=3 in tblOrderProgress where OrderID=1
The result should contain only these parts:
AL11575BC202 - AL11575BC208


Note:
Actual tables have more than half a million records. So performance counts.
A sample database is attached if you need to test.

I appreciate any kind of help.

 

[MajP]

I disagree with this

The result should contain only these parts:
AL11575BC202 - AL11575BC208

qry1and3 those parts with process 3 in order 1 (FYI there is only one part with this case)

SELECT tblorders.partfk,
       tblorders.orderpk,
       tblorderprogress.processfk
FROM   tblorders
       INNER JOIN tblorderprogress
               ON tblorders.orderpk = tblorderprogress.orderfk
WHERE  ( ( ( tblorders.orderpk ) = 1 )
         AND ( ( tblorderprogress.processfk ) = 3 ) );

and QryNotIn!and3

SELECT tblmaster.part
FROM   tblmaster
WHERE  (( ( tblmaster.partpk ) NOT IN ( select partfk from qry1and3 ) ));

 

[arnelgp]

see query2 and the final resulting query, FinalQuery.

 

[deletedT]

I disagree with this
FYI there is only one part with this case)

@MajP I can't understand why you disagree.
this is a simple query:

and this is its' result:

None of *202 & *208 has no record with ProcessFK=3.


Can you be more specific?

 

[deletedT]

see query2 and the final resulting query, FinalQuery.

@arnelgp though your query's result is correct, But FinalQuery returns 12 records. I have no idea what will happen If I run your query on tables with several hundred thousands records. But for sure, that query doesn't help me to track anything because of the amount of shown records even if I limit to the past few years.
And what will happen if I want to print a report based on the query and hand it over to someone?

Is there any way to make the query show only two records? because I have only two orders with this condition.

 

[MajP]

Yes read what you wrote. None of those are in order 1. You said order 1 and process 3.

 

[deletedT]

Yes read what you wrote. None of those are in order 1. You said order 1 and process 3.

Sorry, maybe something's wrong with me today.
But still I can't understand.

This is the exact question from my main post:

I need this query show a list of parts that have no processFK=3 in tblOrderProgress where OrderID=1

 

[MajP]

show a list of parts that have no processFK=3 in tblOrderProgress where OrderID=1

I do not know how to interpret that any other way. It clearly says where processFK = 3 where OrderID = 1. So in tblOrderProgress there is only one record that is in order 1 and has a processFK of 3. That means there are 4 parts that do not have a Process 3 and Order 1.
qry1and3

Part	PartFK	OrderPK	ProcessFK
AL11575BC201	1​	1​	3​

OrderPK = 1 and ProcessFK = 3

I am not sure what you are showing because that seems to answer a question completely unrelated. Seems to answer "For parts with orders in tblOrderProgress need to identify which part does not have a process 3". Does not seem related to the original question. What does OrderID = 1 have to do with anything?

 

[deletedT]

So in tblOrderProgress there is only one record that is in order 1 and has a processFK of 3. That means there are 4 parts that do not have a Process 3 and Order 1.

I think you misunderstood the whole situation.
You're talking about OrderFK in tblOrderProgress.
I'm talking about OrderID in tblOrders.

I posted a screen shot of the relations to make it clear. Please read my main post carefully again .
I think I'm clear enough. If not once again:

I need a list of parts in tblOrderProgress without ProcessFK=3 where their OrderID in tblOrders=1.
In plain English: Which orders in tblOrders have no record in tblOrderProgress with ProcessFK=3?
I hope I'm clear.

@arnelgp got it right. But his solution had a problem. See post #5 above and see if you can help.
I appreciate your time

 

[MajP]

I finally see you have an orderid and an ordetpk. Not something you see to often. Normally the same.

 

[deletedT]

I finally see you have an orderid and an ordetpk. Not something you see to often. Normally the same.

Sorry for the confusion.
In the actual database it was ReceiptionID.
I don't know why I changed it to OrderID while I was creating the sample database.

My apologies.

 

[arnelgp]

make a Total query out of the query FinalQuery.
see FinalQuery2. You only need to display the PartNumber.

 

[jdraw]

SELECT tblMaster.Part
    ,tblOrders.OrderID
    ,tblOrderProgress.RegisteredOn
    ,tblOrderProgress.ProcessFK
    ,tblOrders.OrderPK
FROM tblMaster
INNER JOIN (
    tblOrders INNER JOIN tblOrderProgress
        ON tblOrders.OrderPK = tblOrderProgress.OrderFK
                    )
    ON tblMaster.PartPK = tblOrders.PartFK
WHERE (
        ((tblOrders.OrderID) = 1)
        AND ((tblOrderProgress.ProcessFK) <> 3)
        AND (
            (tblOrders.OrderPK) IN (2 ,3 )
            )
       );

A little late to the thread.

 

[deletedT]

It is Saturday 11:30 PM local time.
I'll check both Monday morning as soon as I'm back to my desk.

Thanks for sharing your experience.

 

[jdraw]

No problem. It's 9:30 AM here.
Good luck. Get some sleep.

 

[deletedT]

@arnelgp thanks it works.

 

[deletedT]

SELECT tblMaster.Part
    ,tblOrders.OrderID
    ,tblOrderProgress.RegisteredOn
    ,tblOrderProgress.ProcessFK
    ,tblOrders.OrderPK
FROM tblMaster
INNER JOIN (
    tblOrders INNER JOIN tblOrderProgress
        ON tblOrders.OrderPK = tblOrderProgress.OrderFK
                    )
    ON tblMaster.PartPK = tblOrders.PartFK
WHERE (
        ((tblOrders.OrderID) = 1)
        AND ((tblOrderProgress.ProcessFK) <> 3)
        AND (
            (tblOrders.OrderPK) IN (2 ,3 )
            )
       );

A little late to the thread.

@jdraw thanks for trying to help but where did (tblOrders.OrderPK) IN (2 ,3 ) came from?
In my actual tables I can't look at a thousand records and add their PK to the search.