Den 2013-01-19 08:27 skrev Gary Benade såhär:

> On 1/18/2013 2:48 PM, Kjell Rilbe wrote:
>> Den 2013-01-18 13:30 skrev Gary Benade såhär:
>>> On 1/18/2013 2:02 PM, Kjell Rilbe wrote:
>>>> Den 2013-01-18 12:56 skrev Kjell Rilbe såhär:
>>>>> Hi,
>>>>>
>>>>> This SQL returns 2533 records, none of which contain B."Kod" starting
>>>>> with 1711 or 2111 (checked thoroughly):
>>>>> select B.*
>>>>> from "Branschkod" B
>>>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>>>> where B."ECO_ID" <> B2."ECO_ID"
>>>>>
>>>>> Now, I add a line to the WHERE:
>>>>> select B.*
>>>>> from "Branschkod" B
>>>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>>>> where B."ECO_ID" <> B2."ECO_ID"
>>>>> and B."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
>>>>>
>>>>> This select DOES return six records, containing B."Kod" starting with
>>>>> 1711 or 2111 (one for each code in the list).
>>>>>
>>>>> How can this happen?
>>>>>
>>>> Note: I tried inserting the results of the first query into a new table.
>>>> Then I did
>>>>
>>>> select *
>>>> from newtable
>>>> where "Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')
>>>>
>>>> It did not return any records. Still the second select does return six
>>>> records.
>>>>
>>>> Kjell
>>> I have a feeling it my be related to nulls in ECO_ID, what do you get
>>> when you run the following query?
>>>
>>> select B.*
>>> from "Branschkod" B
>>> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
>>> where coalesce(B."ECO_ID",'') <> coalesce(B2."ECO_ID",'')
>> No nulls in ECO_ID - it's a pk. Your query returns the same 2533 records
>> as the first SQL above.
> What does this return?
>
> select * from
> {
> select B.*
> from "Branschkod" B
> inner join "Branschkod" B2 on B2."Namn" = B."Namn"
> where B."ECO_ID" <> B2."ECO_ID"
> ) bb
> where bb."Kod" in ('17111', '17112', '17113', '21111', '21112', '21113')

That query returns the same six rows as the one with the "in list"
directly in the innner query. I had already tried that approach, actually.

Regards,
Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64