How to reference subquery in the WHERE clause

Question

I have a SQL query (T-SQL) in which I have a subquery in the SELECT statement.

Here is a reduced version:

SELECT 
    p.id AS id,
    (SELECT
        jobs_without_price.id
    FROM
        st_job jobs_without_price
    WHERE
        jobs_without_price.person_id = p.id
    AND
        jobs_without_price.price IS NULL
    FOR XML AUTO, ROOT('jobs')) AS JobsWithoutPrice
FROM
    st_person p
WHERE
    JobsWithoutPrice IS NOT NULL
GROUP BY 
    p.id

The problem is that SSMS tells me

Invalid column name 'JobsWithoutPrice'.

This is almost certainly because SQL doesn't allow aliases in WHERE clauses, but how else can I use the result of this subquery to filter the results?

[The full query has more table joins and much more in the SELECT statement, all of which pertains to a single person record, whereas the subquery reports back multiple records, which is why it's in a subquery.]

Answer 1

You should be able to to that by simply moving the sub-query in CROSS APPLY operator. Something like this:

SELECT 
    p.id AS id
   ,newXML
FROM
    st_person p
CROSS APPLY
(
    SELECT
        jobs_without_price.id AS JobsWithoutPrice
    FROM
        st_job jobs_without_price
    WHERE
        jobs_without_price.person_id = p.id
    AND
        jobs_without_price.price IS NULL
    FOR XML AUTO, ROOT('jobs')
) AS DS (newXML)
--WHERE
--    newXML IS NOT NULL
GROUP BY 
    p.id;

Note, that I have commented the last WHERE clause. We should in need of it, as CROSS APPLY will return the rows from the left part who a match of a row(s) in the right part (like INNER JOIN). You can try OUTER APPLY for getting all rows from the left part of the operator (like LEFT JOIN).

You are allowed to use the columns returned by the APPLY operator in every clause (SELECT, JOIN, WHERE, ORDER BY, HAVING, GROUP BY).