Good day,
Very sorry, I was sure the query failed the other day.
Well I think I found it: subquery.
create table tableOne(id int auto_increment primary key, val
char(5));
create table tableTwo(a_id int not null, val char(5));
create index on tableTwo(a_id);
insert into tableOne (val) values('1'), ('2'), (null);
insert into tableTwo values(1,'val1'), (2,'val2'), (5,'val3');
/*below fails with
Column "TABLETWO.A_ID" not found; SQL statement:
CREATE FORCE VIEW PUBLIC._66 AS
SELECT
A.ID
FROM PUBLIC.TABLEONE A
WHERE TABLETWO.A_ID IS NULL [42122-155] 42S22/42122
*/
SELECT * FROM (
SELECT A.*
FROM tableOne AS A
LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID
WHERE tableTwo.A_ID Is Null
);
--below executed ok
SELECT * FROM (
SELECT A.*
FROM (
tableOne AS A
LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID
)
WHERE tableTwo.A_ID Is Null
);
drop table tableOne;
drop table tableTwo;
Going back from work yesterday
thinking why most other queries work..
at least the ones used in what i've been tested.
Thankfully I don't use subqueries that much ^^
Hope this helps Thomas.
good day.
On Jun 13, 10:37 pm, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> I can't reproduce the problem with the query you provided. Please post
> a complete, reproducible test case. A reproducible test case
> *includes* all the required "CREATE TABLE" statements and if necessary
> all the data. This includes the problem described by Rami and it
> includes the problem described by Evan. My test case:
>
> drop all objects;
> create table tableOne(id int);
> create table tableTwo(a_id int);
> insert into tableOne values(1), (2), (null);
> insert into tableTwo values(1), (3), (null);
>
> SELECT A.* FROM tableOne AS A
> LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID
> WHERE tableTwo.A_ID Is Null;
>
> Regards,
> Thomas
--
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.
