On Aug 11, 4:26 pm, JavaGuy <[email protected]> wrote: > Hi Ian, > > Look at it this way: > SELECT T1 FROM test2 where ID=3 executed on it's own won't work, since > test2 doesn't have a column T1. > > You're executing it as a nested statement, and it's using the column > T1 from the outer sql statement which is reading from test1 (which > does have a column T1). > > That's why Select * FROM test1 WHERE ID=(SELECT T3 FROM test2 where > ID=3); won't work, since T3 isn't defined in test1 or test2. > > Remember, you can write a statement that says: > SELECT 'x' FROM some_table WHERE some_condition > 'x' will be returned for every record that matches your condition. > > Your inner sql statement returns one record for the where condition > (because test2 has one record with ID = 3). Thus selecting the outer > table's value for T1, which just happens to have the same value as > test1's ID column. Essentially, your select statement just says: > select * FROM test1 where ID = T1 (as long as your inner statement > returns one record) > > Make sense?
It does. Thanks for the explanation. There's also a nice write-up here: http://www.gennick.com/aliases.html. Learnt something new today :-) Cheers, Ian. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
