mysql, to get rows in tblA that aren't in tblB for an item in tblB

I'm trying to get a query to get the batchuuid from the batchTBL that aren't in the JobBatchStatusTBL..

I've tried a couple of different queries trying to use something like:

select * 
from BatchTBL as ba 
left join JobBatchStatusTBL as j 
on ba.BatchUUID=j.BatchUUID
join JobTBL as j2 
on j.JobUUID=j2.JobUUID
where j.batchuuid IS NULL
an开发者_Python百科d j.JobUUID = 'ecd0fab8-8bf1-83cc-b1d7-495034a55618';

but i'm screwing something up...

any thoughts?

thanks

mysql> select BatchName,BatchUUID from BatchTBL;
+-----------+--------------------------------------+
| BatchName | BatchUUID                            |
+-----------+--------------------------------------+
| aa        | d288ff51-d045-d218-52fd-93e3523db85e |
| aa1       | a288ff51-d045-d218-52fd-93e3523db85e |
| aa3       | d188ff51-d045-d218-52fd-93e3523db85e |
| aaa3      | da88ff51-d045-d218-52fd-93e3523db85e |
| baa3      | db88ff51-d045-d218-52fd-93e3523db85e |
| z1        | 7eedfea4-c498-ed6e-f0dd-1397fe7dbd67 |
| d1        | 34781dba-d99c-82e3-f499-b55ded863f81 |
| nb        | 1dd56d9c-daed-7f9f-c13b-246d2ec96513 |
| ds        | cca9a771-b5ef-5926-4c26-21151215a800 |
| a1        | 1bb51584-e68a-21d1-a2df-e07707591b43 |
+-----------+--------------------------------------+
10 rows in set (0.00 sec)

mysql> select JobName,JobUUID from JobTBL;
+---------+--------------------------------------+
| JobName | JobUUID                              |
+---------+--------------------------------------+
| aa      | 8afa9cf4-bf63-a4cd-3cd9-cbc6d17f84be |
| aa1     | ecd0fab8-8bf1-83cc-b1d7-495034a55618 |
+---------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> select JobUUID,BatchUUID from JobBatchStatusTBL;
+--------------------------------------+--------------------------------------+
| JobUUID                              | BatchUUID                            |
+--------------------------------------+--------------------------------------+
| ecd0fab8-8bf1-83cc-b1d7-495034a55618 | d288ff51-d045-d218-52fd-93e3523db85e |
+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

thanks

Your query puts an inner join on JobBatchStatusTbl to JobTbl, so rows that are in one but not the other will never be returned at all. You only need two of the tables for this:

SELECT * 
  FROM BatchTbl 
 WHERE JobUUID 
   NOT 
    IN (SELECT JobUUID 
          FROM JobBatchStatusTBL) 

I should note that it's impossible to use the JobUUID in the WHERE clause as you're attempting in your initial query, because this is returning only batches for which there is no corresponding job, according to your post - which makes me wonder if your post was misworded, since you had a specific job uuid in your query?

On another note, you should never name tables, columns or anything else in a way that describes their schema type.

Try

select * from BatchTBL where JobUUID not in (select JobUUID from JobBatchStatusTBL)

If I understand what you are trying to do correctly. To "get a query to get the batchuuid from the batchTBL that aren't in the JobBatchStatusTBL"

use the not in SQL syntax. It is easier to write and read, and the optimizer will take care of the rest.

Select BatchName,BatchUUID from BatchTBL where BatchUUID not in (Select BatchUUID from JobBatchStatusTBL)