Hi All, I have the following query.when I run this it does not return any rows.SELECT * from Table 1 where fieldx in(SELECT fieldx from table2 where key=27);when I hard code the values as SELECT * from Table 1 where fieldx in(4,5); it returns the data.what could be the reason for this.grately appriciate the help tx C
Welcome to the forum! Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN.
Also keep in mind that uniqueness of primary key column values in left table or right table would decide number of records returned in result set.
[quote user="FrankKalis"] Welcome to the forum! Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN. [/quote] It can return rows even if there are null values retured from subqueryselect * from ( select 6 as n union all select 74 ) as t1 where n in ( select null as n union all select 6 )
[quote user="Madhivanan"] [quote user="FrankKalis"] Welcome to the forum! Can there be NULL values in table2? If so, the whole IN clause evaluates to NULL and no row is returned. I would rewrite the IN clause to a JOIN. [/quote] It can return rows even if there are null values retured from subquery select * from ( select 6 as n union all select 74 ) as t1 where n in ( select null as n union all select 6 ) [/quote] Aargh, my bad. Was thinking of NOT IN. Thanks for correcting me.
thanks for the reply.still the same issue.when I run the sub query for the union it returns the values 3,4..but the whole query return no rows.
hi there are no null values in the second table.when I run a select in the second table it returns me 2,4..but when I include it in the in clouse no rows returned
[quote user="chandis"]SELECT fieldx from table2 where key=27 [/quote] Run this query and I'm almost certain then you will get your answer.....
[quote user="chandis"]SELECT * from Table 1 where fieldx in(SELECT fieldx from table2 where key=27); when I hard code the values as SELECT * from Table 1 where fieldx in(4,5); it returns the data.what could be the reason for this.grately appriciate the help[/quote] hey Chandi, If you go step by step, you can get your result. Let's start with the subquery SELECT fieldx from table2 where key=27, as you said it returns 2,4.. then the main query becomes SELECT * from Table 1 where fieldx in(2,4,....); Right? Then As you said when you applied SELECT * from Table 1 where fieldx in(4,5); It returns some data then the above query should returns data for key id 4 Note: Please check again your script and if possible just post Table script and some sample data.(select statement) Thank you, Sandy.