On 1/20/2013 10:31 PM, Kjell Rilbe wrote: > 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
If the query works as a subquery then it has to work as a query, lets try something else. Is it possible that your client is not returning all of the results? Can you try: select B.* from "Branschkod" B inner join "Branschkod" B2 on B2."Namn" = B."Namn" where B."ECO_ID" <> B2."ECO_ID" order by bb."Kod" to see if Kod's in the 17111 range are getting returned, or if the resultset ends before it reaches that range 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/