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

asked 18 Apr '12, 16:17

tdahnert's gravatar image

tdahnert
16114
accept rate: 50%

edited 15 Mar '13, 18:31

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

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
(18 Apr '12, 17:12) Volker Barth

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

permanent link

answered 18 Apr '12, 17:41

tdahnert's gravatar image

tdahnert
16114
accept rate: 50%

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.

(18 Apr '12, 17:58) tdahnert

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

(19 Apr '12, 14:37) Volker Barth

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

permanent link

answered 18 Apr '12, 16:54

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 18 Apr '12, 16:56

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.

(18 Apr '12, 17:45) tdahnert

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

permanent link

answered 18 Apr '12, 17:00

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

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

(18 Apr '12, 17:55) tdahnert
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×47
×21
×5

question asked: 18 Apr '12, 16:17

question was seen: 1,130 times

last updated: 15 Mar '13, 18:31