|
| Help with SQL query - HFCS |
| Iniciado por guest, 27,oct. 2016 03:18 - 4 respuestas |
| |
| | | |
|
| |
| Publicado el 27,octubre 2016 - 03:18 |
Hi
I have to find barcode records which are not linked to a stockcode so I have the following querySELECT 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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 27,octubre 2016 - 08:39 |
Hi David,
try this syntax.
SELECT STKPLU.plu
FROM STKPLU
WHERE stkplu .sysid NOT IN (SELECT sysid FROM stkmast) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 27,octubre 2016 - 09:04 |
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. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 27,octubre 2016 - 22:24 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 28,octubre 2016 - 00:07 |
Well, I didn't know which files was "barcode records" and wich one "stockcode".
It was just the contrary! |
| |
| |
| | | |
|
| | | | |
| | |
|