Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, is there a way that I can combine NOT EXISTS, looking for a value in two columns, and EXISTS all in the same WHERE clause? For example: I need to know, from Table A, which Items A AND Items B do not exist in Table B Items, BUT which Prefix A from Table A exist in Table B Prefix. In other words, from Table A, which Items of A and B DO NOT EXITS and Prefix A does EXISTS in Table B. This is going in the LOAD scriptIf anyone could point me in the right direction. Thanks!
Table A:
Items A | Prefix A | Items B | Prefix B |
1653-46 | 1653 | 6214-58 | 6214 |
1653-56 | 1653 | 1856-25 | 1856 |
3256-98 | 3256 | 8123-73 | 8123 |
8523-14 | 8523 | 3254-94 | 3254 |
5648-87 | 5648 | 2145-84 | 2145 |
7892-92 | 7892 | 1125-44 | 1125 |
3152-35 | 3152 | 4563-70 | 4563 |
7559-78 | 7559 | 7823-46 | 7823 |
6352-74 | 6352 | 5613-43 | 5613 |
7325-19 | 7325 | 3152-20 | 3152 |
Table B:
Items | Prefix |
1653-56 | 1653 |
2145-84 | 2145 |
7559-78 | 7559 |
3152-20 | 3152 |
Results:
Items A | Prefix A | Items B | Prefix B |
1653-46 | 1653 | 6214-58 | 6214 |
3256-98 | 3256 | 8123-73 | 8123 |
8523-14 | 8523 | 3254-94 | 3254 |
7892-92 | 7892 | 1125-44 | 1125 |
3152-35 | 3152 | 4563-70 | 4563 |
6352-74 | 6352 | 5613-43 | 5613 |
Hi,
with the logic explaine, in the result the line
3256-98 | 3256 | 8123-73 | 8123 |
must not exist PrefixA=3256 not exist in the TableB.
So, I propose :
Table_A:
load * inline [
ItemsA, PrefixA,ItemsB,PrefixB
1653-46,1653,6214-58,6214
1653-56,1653,1856-25,1856
3256-98,3256,8123-73,8123
8523-14,8523,3254-94,3254
5648-87,5648,2145-84,2145
7892-92,7892,1125-44,1125
3152-35,3152,4563-70,4563
7559-78,7559,7823-46,7823
6352-74,6352,5613-43,5613
7325-19,7325,3152-20,3152
];
Table_B:
load * inline [
Items,Prefix
1653-56,1653
2145-84,2145
7559-78,7559
3152-20,3152
];
output:
NoConcatenate
LOAD * Resident Table_A WHERE Not EXISTS(Items,ItemsA) and Not EXISTS(Items,ItemsB) and EXISTS(Prefix,PrefixA)
;
DROP table Table_A,Table_B;
and the output :
Hi,
with the logic explaine, in the result the line
3256-98 | 3256 | 8123-73 | 8123 |
must not exist PrefixA=3256 not exist in the TableB.
So, I propose :
Table_A:
load * inline [
ItemsA, PrefixA,ItemsB,PrefixB
1653-46,1653,6214-58,6214
1653-56,1653,1856-25,1856
3256-98,3256,8123-73,8123
8523-14,8523,3254-94,3254
5648-87,5648,2145-84,2145
7892-92,7892,1125-44,1125
3152-35,3152,4563-70,4563
7559-78,7559,7823-46,7823
6352-74,6352,5613-43,5613
7325-19,7325,3152-20,3152
];
Table_B:
load * inline [
Items,Prefix
1653-56,1653
2145-84,2145
7559-78,7559
3152-20,3152
];
output:
NoConcatenate
LOAD * Resident Table_A WHERE Not EXISTS(Items,ItemsA) and Not EXISTS(Items,ItemsB) and EXISTS(Prefix,PrefixA)
;
DROP table Table_A,Table_B;
and the output :