SQL WHERE Subquery in Field List

Question

I have query like:

SELECT field
FROM table
WHERE
    (
        SELECT COUNT(*)
        FROM table2
        WHERE table2.field = table.field
    )
    !=
    (
        SELECT COUNT(*)
        FROM table3
        WHERE table3.field = table.field
    )

Now I want to have those WHERE subqueries in my field list like:

SELECT field, count1, count2
FROM table
WHERE
    (
        SELECT COUNT(*)
        FROM table2
        WHERE table2.field = table.field
    ) AS Count1
    !=
    (
        SELECT COUNT(*)
        FROM table3
        WHERE table3.field = table.field
    ) AS Count2

Is this possible? Of course I could put those subqueries in the field list, but then I can't compare them.

Any ideas?

Answer 1

You can do this if you use Sql Server:

SELECT field, ca2.c2, ca3.c3
FROM table t
cross apply(SELECT COUNT(*) c2
            FROM table2 t2
            WHERE t2.field = t.field)ca2
cross apply(SELECT COUNT(*) c3
            FROM table3 t3
            WHERE t3.field = t.field)ca3
where ca2.c2 <> ca1.c1