Help with SQL query - HFCS

Startbeitrag von David Egan am 27.10.2016 01:18

Hi
I have to find barcode records which are not linked to a stockcode so I have the following query
SELECT STKPLU.plu
FROM STKPLU
WHERE NOT EXISTS (
SELECT * FROM stkmast m
join stkplu p on (p.stkm_linkid = m.sysid)
)

This query returns 0 records. The STKPLU file contains 16,335 records which is also what I get if I remove the NOT. If I run the sub-query on its own I get 16,031 records. The difference between the 2 is what I would expect my query to return.

What am i missing?

Thanks

David

Antworten:

Hi David,

try this syntax.

SELECT STKPLU.plu
FROM STKPLU
WHERE stkplu .sysid NOT IN (SELECT sysid FROM stkmast)

von Arie - am 27.10.2016 06:39
I think that you don't need a subquery, but to use the "IS NULL" operator.

Try this:


SELECT *
FROM stkmast
LEFT OUTER JOIN stkplu
ON stkplu.stkm_linkid = stkmast.sysid
WHERE STKPLU.stkm_linkid IS NULL


Regards,
José Antonio.

von Jose Antonio Garrido - am 27.10.2016 07:04
Thanks guys
Arie, that was what I tried originally but that returned 16,335 records so I then started playing around with NOT EXISTS.
Jose, that only returned one record but it did get me on the right track. Twisting it around as follows got me 304 records. I am curious though as to why Arie's suggestion and my previous one didn't work.
SELECT stkplu.plu FROM stkplu
LEFT OUTER JOIN stkmast on (stkmast.sysid = stkplu.stkm_linkid)
WHERE stkmast.sysid is null


Cheers

David

von David Egan - am 27.10.2016 20:24
Well, I didn't know which files was "barcode records" and wich one "stockcode".
It was just the contrary!

von Jose Antonio Garrido - am 27.10.2016 22:07
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.