0

Question edited

#TEMP_TABLE data: | Serial Number | ORIG | DEST | | ------------- | ---- | ---- | | 12345 | CAN | YOW | | 67890 | YYZ | PEK | | 13579 | PEK | YVR | | 24680 | PVG | YOW | | 15764 | YVR | XIY | | 24863 | CTU | PVG |

I was able to get my expected results by:

SELECT * FROM #TEMP_TABLE
WHERE
(ORIG IN ('PEK','PVG') AND LEFT(DEST,1)<>'Y')
OR
(ORIG NOT IN ('PEK','PVG') AND LEFT(ORIG,1)<>'Y')
OR
(ORIG NOT IN ('PEK','PVG') AND LEFT(ORIG,1)='Y' AND DEST NOT IN ('PEK','PVG'));

Expected results: | Serial Number | ORIG | DEST | | ------------- | ---- | ---- | | 12345 | CAN | YOW | | 15764 | YVR | XIY | | 24863 | CTU | PVG |

Looking to a different way to achieve the same results by using nested query:

SELECT * FROM #TEMP_TABLE
WHERE
(ORIG IN ('PEK','PVG') AND LEFT(DEST,1)<>'Y')
OR
(ORIG NOT IN ('PEK','PVG') AND DEST IN
    (SELECT DEST FROM #TEMP_TABLE
        WHERE LEFT(ORIG,1)='Y' AND DEST NOT IN ('PEK','PVG'))
);

But it gives me a different result: | Serial Number | ORIG | DEST | | ------------- | ---- | ---- | | 15764 | YVR | XIY |

Winniethewinner
  • 7
  • 2
  • 1
    "but it's not giving me the correct results.", it's giving you an error, why didn't you tell us that, and included the error message? – HoneyBadger Aug 24 '22 at 17:49
  • it's giving the results: 12345, 67890, 15764, 24863. but 67890 shouldn't be because it's not meeting criteria of LEFT(ORIG,1)=Y AND DEST NOT IN (PEK,PVG) – Winniethewinner Aug 24 '22 at 17:56
  • The SQL in the question does not give the result stated in the comment. (It would return only 15764 if you simply added the missing quotes to avoid a syntax error.) Edit the question rather than putting details in a comment. BTW - example data should be supplied as text. Only use images when visual content is important. – Fred Aug 24 '22 at 22:01
  • Can `orig` or `dest` be null? – shawnt00 Aug 25 '22 at 20:26
  • I edited my question, but not sure why the table is not displayed - I had it displayed in HTML in preview when I edited. – Winniethewinner Aug 26 '22 at 15:52

1 Answers1

1

I'm assuming the the characters in NOT IN clause should require 'PEK','PVG' along with your other NOT IN statement. And the Y would probably need 'Y'.

SELECT [SerialNumber] FROM TESTTABLE
WHERE ORIG NOT IN ('PEK','PVG')
AND LEFT(ORIG,1)='Y' AND DEST NOT IN ('PEK','PVG')
VLOOKUP
  • 548
  • 4
  • 12