CODEWITHSUNDEEP
mysqlsqlselect
David Jansson
David Jansson

Reputation: 37

How to select records that are not in another table?

I need to compare two tables and get the pn's that are not done or non existent. I cant seem to find an answer online anywhere for exactly what I need. Here are the tables and the example output that I need. Thank you so much to whom ever can help me out.

Table1:

+------------+----------+------------+----+---------------------+
| cert       | job      | pcmk       | pn | stat                |
+------------+----------+------------+----+---------------------+
| MF21600001 | 6216     | A148       | 1  | 2015-08-14 13:20:29 |
| MF21600001 | 6216     | A148       | 2  |                     |
+------------+----------+------------+----+---------------------+

Table2:

+-----------+----------+----+
| job       | pcmk     | pn |
+-----------+----------+----+
| 6216      | A148     | 1  |
| 6216      | A148     | 2  |
| 6216      | A148     | 3  |
+-----------+----------+----+

Example output for rows in Table2 that are not in Table1 or status = blank/NULL:

+------------+------+------+----+
| cert       | job  | pcmk | pn |
+------------+------+------+----+
| MF21600001 | 6216 | A148 | 2  |
| MF21600001 | 6216 | A148 | 3  |
+------------+------+------+----+

OK, I took the first idea and played with it a bit.

SELECT Table1.cert, pninput.job, pninput.pcmk, pninput.pn, pn.stat
FROM   Table1, Table2
WHERE  NOT EXISTS (SELECT *
                   FROM   Table1
                   WHERE  Table1.pcmk = Table2.pcmk AND Table1.job = Table2.job AND Table1.stat = '')

However now every cert gets paired up with every pcmk and job even though every cert can only have 1 job and 1 pcmk and it also taked 35+ seconds to run

Upvotes: 0

Views: 456

Answers (3)

seahawk
seahawk

Reputation: 1912

Clarification: One job can have multiple pcmk and comparision should be done for each pcmk

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

Assumption: Query is for single job. For multiple job, add job check in where-clause,on-clause' andgroup by` as well.

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16730

One way you could approach this is by getting a list of pn values from t1 that are not null:

SELECT pn
FROM table1
WHERE stat IS NOT NULL;

Then you can pull all values from table2 that are not in that group. This will account for all pn values that are not in table1 at all, as well as the values that have a null status in table1:

SELECT *
FROM table2
WHERE pn NOT IN(
  SELECT pn
  FROM table1
  WHERE stat IS NOT NULL);

Here is an SQL Fiddle example.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312136

If I rephrase your condition, you want the rows from table2 that don't have a corresponding row in table with a non-null status. This sounds like an exists condition:

SELECT *
FROM   table2
WHERE  NOT EXISTS (SELECT *
                   FROM   table1
                   WHERE  table1.pn = table2.pn AND stat IS NOT NULL)

Upvotes: 1

Related Questions