On 25/02/2009 6:15 AM, John Elrick wrote: > I may be overlooking something obvious, however, I cannot discern from > the documentation if this is possible. > > given a simple example: > > create table x (x_id integer); > create table y (y_id integer, y_value varchar); > > insert into x values (1); > insert into x values (2); > insert into y values (1, 'Hello world'); > > select case when > (select y_value from y where y_id = x_id) > is null then > 'darn' > else > (select y_value from y where y_id = x_id) > end > from x > > > Is there any way to eliminate the second (select y_value from y where > y_id = x_id)? If so, what would the query look like? >
The bog-standard should-work-everywhere SQL way is to use the COALESCE function; see http://en.wikipedia.org/wiki/Null_(SQL) Sqlite has COALESCE and also IFNULL; see http://www.sqlite.org/lang_corefunc.html sqlite> create table foo (id integer, data text); sqlite> insert into foo values(5, 'bar'); sqlite> insert into foo values(6, null); sqlite> select * from foo; 5|bar 6| sqlite> select coalesce((select data from foo where id = 6), 'darn'); darn sqlite> select coalesce((select data from foo where id = 5), 'darn'); bar sqlite> HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
