create table t1 (a1 int, a2 char(8));
|
create table t2 (b1 int, b2 char(8));
|
|
insert into t1 values (0, '2 - 00');
|
insert into t1 values (1, '2 - 01');
|
insert into t1 values (2, '2 - 02');
|
|
insert into t2 values (1, '2 - 01');
|
insert into t2 values (1, '2 - 01');
|
insert into t2 values (2, '2 - 02');
|
insert into t2 values (2, '2 - 02');
|
insert into t2 values (3, '2 - 03');
|
|
# ok
|
select * from t1 having a1 > 0;
|
# ok
|
select * from t2 where exists (select * from t1 where a1 > 0);
|
# ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause'
|
--error ER_BAD_FIELD_ERROR
|
select * from t2 where exists (select * from t1 having a1 > 0);
|
|
# ok
|
select * from t2 where exists (select * from t1 where a1 = b1);
|
# ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause'
|
--error ER_BAD_FIELD_ERROR
|
select * from t2 where exists (select * from t1 having a1 = b1);
|
# ok
|
select * from t2 where b2 in (select a2 from t1 group by a1 having a1 = b1);
|
# ok
|
select * from t2 where b1 in (select a1 from t1 group by a1 having a1 = b1);
|
|
drop table t1, t2;
|