Re: SQL Question - subquery on a combination of columns

Hi Neil ! You can try this way... ##----------------------------------------------------------## SELECT * FROM table1 WHERE col_a||col_b in (SELECT col_x||col_y FROM table2); ##----------------------------------------------------------## Good Luck, Nayan Jain - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "Where there is great love, there are always wishes." -Willa Cather On Wed, 9 Jun 1999 neil_smith@my-deja.com wrote: > I come from an Oracle background, and I am just getting to grips with > some of the different Informix syntax. I have worked out most things, > but I can't seem to do a subquery on a combination of columns. > > As an example, if I have two tables: > > table1(col_a, col_b, ...) > table2(col_x, col_y, ...) > > I want to select all rows of tabe1 where the combiantion of col_a and > col_b exist as col_x and col_y in table2. > > In Oracle I could write a query like: > > select * from table1 > where (col_a, col_b) in ( > select col_x, col_y from table2 > );> > In Informix, this syntax is rejected. Is it possible to do this sort > of query in Informix? > > Note that I am hoping that there is just a different syntax for doing > the same sort of subquery on multiple columns. I know that in the > simple example above I could have rewritten the query just by joining > the tables in the main select, or by having a correlated EXISTS clause > instead of the IN subquery. > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. >