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.
>
> Kjell
>
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')
Regards
G
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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/
