CODEWITHSUNDEEP
sqlcountwhere-clause
Ritesh
Ritesh

Reputation: 11

specifying count in WHERE clause

select *
from table1 t1,
    table2 t2,
    table3 t3
where t2.parent_id = t1.row_id
    and t2.xyz is not null
    and (
        select count(*)
        from table3
        where xyz = t2.row_id
        ) = 0;

Will it work? I am using the alias t2 within my subquery.

My requirement is to check is to specify condition in where clause such that there is no record present in table3 where column xyz of table3 is stored as row_id of table2.

Upvotes: 1

Views: 64

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

You can use NOT EXISTS to assert that there is no row returned from the subquery. Use modern explicit join syntax instead of comma based legacy syntax. No need to join table3 outside (you were making a cross join effectively).

select *
from table1 t1
join table2 t2 on t2.parent_id = t1.row_id
where t2.xyz is not null
    and not exists (
        select 1
        from table3
        where xyz = t2.row_id
        );

Upvotes: 2

Related Questions