Comparing 2 tables MySQL

[DavJans]

Hi and thank you all in advance, I'm struggling to find the best way and how to compare 2 tables for the result I want.
We have 1 table(QC) which gets filled out by our quality control department, we also have another table(PN) that we add a list production notes to.
Only two columns in both tables match, JobNumber and PieceMark.
What I'm trying to do is compare the two and in a third table(Open PN's) we want add the pieces where the Production note is not done.
In fact there are actually 3 tables already(third one is pn's already linked to the piece, APN(Attached PN's), How would I populate the 4th?

Table QC
ID, Job, Piecemark
1, 6216, A53

Table APN
ID, PNn, Done
1, 1, Done
1, 2, Done
1, 3,
(PNn 4 hasn't been populated in this table yet, at this time in my program this gets populated when someone looks at the record in Table QC Not when it is added to the Table PN)

Table PN
Job, Piecemark, PNn
6216, A53, 1
6216, A53, 2
6216, A53, 3
6216, A53, 4

Those are the tables we have now, What I need is to populate a table or just a list? with only the open pn's

Table OPN
ID, Job, Piecemark, PNn
1, 6216, A53, 3
1, 6216, A53, 4

[ghettocottage]

Your example just shows a single Job Number. Are these query results you will be working with to get the final list you are after, or will there be multiple jobs..so your final list will show multiple jobs as well as Piecemarks ?

In other words,

Either way, I think you will be able to get your final list as a query rather than having to have a new table,,,if I am understanding this correctly...

It sounds like you want to build a query to get a specific Job (or maybe multiple jobs) and Piecemark for each PN that has not been marked as Done

Does that sound correct?

[DavJans]

Sorry for late response, I was out for a day, I got this to work but it is slow, 30 to 45 seconds. But to clarify. There are many jobs, and many piece marks per job.
Each PN(Production Note) that we get is for 1 job only and usually has 2 or 3 piece marks on it.
Each job however can have the same piece mark (different piece but sometimes the same number)

Here is the query that I got to work. But like I said, it takes a long time to run.

Code: Select all

SELECT t1.cert,t2.job,t2.pcmk,t2.pn
    FROM pn t1
    join pninput t2 on (t1.pcmk=t2.pcmk AND t1.job=t2.job)
    WHERE (t1.pn=t2.pn and (t1.stat="" or t1.stat is null)) 
    or t2.pn NOT IN(
      SELECT pn
      FROM pn
      WHERE pn.job=t2.job)
      group by t1.cert,t2.pn;

In this query pn t1 is Table APN and pninput t2 is Table PN

If there is a faster way to do it that would be great. As it stands however I think running a script on the server a few times a day to update a table for our employees to look at would be much better. I think they would complain about having to wait a minute every time they want to look at this list.

[ghettocottage]

You could possibly create a trigger(s) in MySQL to run when your tables are updated...which updates your new table. That way all the data would be kept current in real-time

[DavJans]

That sounds cool, i'll try to figure out how to do that. first though I need to figure out how to safe query results into a table.

[ghettocottage]

Or just add new items to your new table during your other event where things are updated:

.. at this time in my program this gets populated when someone looks at the record in Table QC..