SQL-query returns a value instead of comparing against it

Question

I have this query:

SELECT  web.KONTO, LAGKART.VARENUMMER, LAGKART.GRUPPE, LAGPRIS.PRIS, LAGKART.VARENAVN1, LAGPRIS.LXBENUMMER, LAGPRIS.ENHED
FROM    LAGKART
INNER JOIN
        LAGPRIS
ON      LAGKART.VARENUMMER = LAGPRIS.VARENUMMER
INNER JOIN
        DEBWEBVARER web
ON      web.VARENUMMER = LAGPRIS.VARENUMMER
WHERE   LAGPRIS.ENHED = web.ENHED
        AND web.KONTO = '  00000001'
ORDER BY
        LAGKART.VARENAVN1 ASC

And it yields:

  KONTO   |VARENUMMER|GRUPPE|PRIS          |VARENAVN1|LXBENUMMER|ENHED

  00000001|003       |1     |0.000000000000|765402   |stk

  00000001|003       |1     |0.000000000000|4418625  |kg

The problem is:

In table DEBWEBVARER there are 0 rows containing KONTO = ' 00000001'

Anyone able to figure out why it specifies KONTO as ' 00000001' instead of comparing?

Answer 1

What field type is KONTO, sounds like customer. If this is an int then both values are 1 and thus the same.

Also, is that whitespace in KONTO = ' 00000001' meant to be there or is that simply from editing text for answer?

You may wish to explicitly set comparison to type varchar and then compare (using sql convert or cast operators on both KONTO and the string you wish to match).

Answer 2

In table DEBWEBVARER there are 0 rows containing KONTO = ' 00000001'

There is at least one row containing KONTO = ' 00000001' (with 2 leading spaces), which is returned by your query.

If you issue this query:

SELECT  *
FROM    DEBWEBVARER web
WHERE   web.KONTO = '  00000001'

, you can see what does this row (or rows) look like.

Could you also please issue the following query:

SELECT  TOP 1
        CAST(KONTO AS BINARY),
        CAST('  00000001' AS BINARY),
FROM    DEBWEBVARER web
WHERE   web.KONTO = '  00000001'

again, copying and pasting your filter into both WHERE and SELECT clauses?

Answer 3

Issue solved:

The DB-admin has setup some wierd filtering on the DB. I have no idea what he is doing, but apparently when I select everything it only return 253 rows, when there are infact 33000 rows in the table.

So the rows with KONTO = '  00000001' are infact there, but didn't show up when I selected "Show all table data".

Thank you for your answers, and sorry to have wasted your time in this case :)