Simple Syntax Question

[Former Member]

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

[VolkerBarth]

Just to add:

That simple, self-joining query would not need a derived query at all - it is identical to

Select Myquery.field1, table1.*  
from table1 as MyQuery join table1 on table1.field2 = MyQuery.field1

In order to express the self-join, I would use an alias for the 2nd instance of table1, as well, such as:

Select T1.field1, T2.*  
from table1 as T1 inner join table1 as T2 on T2.field2 = T1.field1

[Former Member · Accepted solution]

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.

My solution:

Select * from (select field1 from table1) as MyQuery (MyNamedField) join table1 on table1.field2 = MyQuery.MyNamedField

[Former Member · Accepted solution]

I also learned a better description of what I was trying to do is "derived tables". I'm adding it to help googlers in the future.

[Former Member · Accepted solution]

Link that allowed me to find the answer.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1530/doc/html/san12...

[VolkerBarth · Accepted solution]

Just for the record: What version are you using that does not allow the syntax as cited in my response?

(Or is this due to the fact that the real query is semantically very different from the samples here?)

[MarkCulp]

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 );

HTH

[Former Member]

I didn't post the full query in my question, because I was concerned the complexity would throw people off when the syntax issue I was having problem with is straightforward. The problem I was trying to solve involves a table where one field may be duplicated on many records. If the field is duplicated, I only want to return one record. The record returned should have the maximum value in a different field within it's group. If that's unclear, here is a more relavent example of the query I was trying to get.

Select * from (Select Max(UniqueField), NonUniqueField from Table1 group by NonUniqueField) as UniqueSubset (MaxUniqueField, NonUniqueField) left join Table1 on UniqueSubset.MaxUniqueField = Table1.UniqueField and UniqueSubset.NonUniqueField = Table1.NonUniqueField

The "in" command doesn't really accomplish this well because it's a composite key I'm after (two fields).

[VolkerBarth]

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 12.0.1.3554:

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...)

[Former Member]

I'm actually using left joins in my real query and the order is as you suggested. I was just trying to keep the example as simple as possible. Thank you again for your answer even though the syntax didn't work in my application.