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. Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: [email protected] Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
