Missing data row in NOT IN clause

Question

I just realised that my Auto-Price Calculation doesn't fill the Prices for ListID 4. I inserted the Prices from the SELECT shown below.

For bugg research I executed the SELECT without the WHERE part and it shows me the example data row.

I can't find the error though, why it is not shown in the complete select (it has no entry with ListID = 4).

Someone can see my mistake?

Not In Error

Edit: Just tried the subselect alone, it shows no rows for the requested article. Why is the NOT IN clause unaffected by this fact?

MatBailie · Accepted Answer · 2014-03-12T16:37:34.093

0

Most likely, it's because of how you are combining the artikelnummer and the auspraegungID.

I do not think you have accounted for that fact that '100' + '0' is idential to '10' + '00'.

Instead of trying to merge two fields into one, perhaps try the following?

SELECT
  *
FROM
  #allArticles  AS allArticles
WHERE
  Artikelnummer = 'IT-810260'
  AND NOT EXISTS (SELECT *
                    FROM KHKPreisListenArtikel
                   WHERE ListeID       = 4
                     AND Artikelnummer = allArticles.Artikelnummer
                     AND Auspraegung   = allArticles.Auspraegung
                 )

If that still doesn't work, then you must have corresponding records in that other table, find them like this...

SELECT
  *
FROM
  #allArticles            AS allArticles
INNER JOIN
  KHKPreisListenArtikel   AS Preis
    ON  Preis.ListeID       = 4
    AND Preis.Artikelnummer = allArticles.Artikelnummer
    AND Preis.Auspraegung   = allArticles.Auspraegung
WHERe
  allArticles.Artikelnummer = 'IT-810260'

PLEASE ALSO NOTE

Please don't include images of code, please copy the code, so that we can copy it too.

Especially when the tables/fields are in another language...

EDIT

Here is a query that will show the cause of your original query to fail.

SELECT
  *
FROM
  #allArticles            AS allArticles
INNER JOIN
  KHKPreisListenArtikel   AS Preis
    ON  Preis.ListeID       = 4
    AND Preis.Artikelnummer       + CONVERT(VARCHAR(50), Preis.Auspraegung)
        =
        allArticles.Artikelnummer + CONVERT(VARCHAR(50), allArticles.Auspraegung)
WHERE
  allArticles.Artikelnummer = 'IT-810260'

edited Mar 12 '14 at 16:37

answered Mar 12 '14 at 16:18

MatBailie

83,401
18
103
137

Article numbers have a unique schema. This can't be the problem I guess. Is it possible that the IN Operator does not work on varchar values? – Simon Pawlowski Mar 12 '14 at 16:26
Solution 2 does not work, since I want only the Articles with NO price in List 4. Solution 1 does work though, thanks a lot! – Simon Pawlowski Mar 12 '14 at 16:31
@SimonPawlowski - The `IN` operator will work fine. But, your concatenation is still a potential hazard; In your example you have a row of `artikelnummer = 'IT-810620', Auspraegung = 0`, and when concatenated you get `composite_key = 'IT-8106200'`. If then you have a record in your other table as follows, you have a problem; `artikelnummer = 'IT-8106', Auspraegung = 200` because that will yield the same result of `composite_key = 'IT-8106200'` – MatBailie Mar 12 '14 at 16:34
@SimonPawlowski - Solution 2 isn't a solution! It's the code you could run to find any matches that could cause first query to fail! – MatBailie Mar 12 '14 at 16:35
I know the risks of this and I usually try to avoid it, but in this scenario its not possible to happen. – Simon Pawlowski Mar 12 '14 at 16:35
@SimonPawlowski - The fact that your query failed and the NOT EXISTS query worked suggests otherwise. What do you get from the results of my final edit? – MatBailie Mar 12 '14 at 16:38

Missing data row in NOT IN clause

1 Answers1