I've worked with Microosft SQL for years and am writing my first SQL Anywhere application. There is a simple query I'm trying to write that I can't get to work without syntax issues. Can someone please show me the correct syntax? I can't seem to get a named subquery to work
Select * from (select field1 from table1) as MyQuery join table1 on table1.field2 = MyQuery.field1
Thank you for everyone's help. It appears that the version I was working against wanted me to name the columns using a syntax I was unfamiliar with.
Select * from (select field1 from table1) as MyQuery (MyNamedField) join table1 on table1.field2 = MyQuery.MyNamedField
answered 18 Apr '12, 17:41
That exact syntax should work - what version are you using (run select @@version) and what error message do you receive?
For example, here is a sample query that uses a similar query against the system catalog, and it does work with v 18.104.22.16854:
select * from (select table_id, table_name from systable) as ST join syscolumn on syscolumn.table_id = ST.table_id where table_name = 'dummy'
Note that the "as" before the derived query's alias (here "ST") is optional, however the alias itself is necessary.
(FWIW, I would usually use "inner join" instead of just "join" - in order to distinguish that from outer joins, and would order the join condition in the same order as the according tables/derived queries, i.e. I would swap the on condition...)
What exactly are you trying to get as a result set? It looks like you want the rows from table1 that have a field1 value equal to any of the values in the field2 column of the same table?
If the above is correct, then try this:
select * from table1 where field1 in ( select distinct field2 from table1 );
answered 18 Apr '12, 17:00