Need to use query column value in nested subquery

Question

EDIT
Sorry for the half post :(

It seems I cannot use a column from the parent query in a sub query. How can I refactor this query to get what I need?

dbo.func_getRelatedAcnts returns a table of related accounts (all children from a given account). It goes up to the top level parent account of a given account and then selects all child accounts from there. This gives me a table of all the parent, sibling, and child account ids.

Events has a foreign key to an Account and Profiles has a foreign key to accounts.

Registrations have a foreign profile key and event key. There can be multiple regs per profile. I need to find all the regs that are associated with profiles that are in accounts that are not in any way related hierarchically to the event the registration is in.

The problem is that I want to use the profile account key of profile related to the reg in the parent query to dynamically grab the table in the subquery.

SELECT COUNT(r.reg_id)  
FROM registrations r 
JOIN profiles p ON (r.reg_frn_pro_id = p.pro_id)  
JOIN events e ON (r.reg_frn_evt_id = e.evt_id)  
WHERE evt_frn_acnt_id NOT IN 
      (SELECT * FROM dbo.func_getRelatedAcnts(p.pro_frn_acnt_id))  

My error:
pro_frn_acnt_id is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

Answer 1

Not entirely sure why you have two sets of parentheses, and unsure of what you're trying to match up, but one of the following two should work, and if not, it will give you the general idea of what needs to be done.

1)

SELECT COUNT(r.reg_id)
FROM registrations r JOIN profiles p ON (r.reg_frn_pro_id = p.pro_id)  
JOIN events e ON (r.reg_frn_evt_id = e.evt_id)  
WHERE evt_frn_acnt_id NOT IN 
    ( SELECT pro_frn_acnt_id FROM dbo.func_getRelatedAcnts )

2)

SELECT COUNT(r.reg_id)
FROM registrations r JOIN profiles p ON (r.reg_frn_pro_id = p.pro_id)  
JOIN events e ON (r.reg_frn_evt_id = e.evt_id)  
WHERE evt_frn_acnt_id NOT IN 
    ( SELECT evt_frn_acnt_id FROM dbo.func_getRelatedAcnts )

Answer 2

This works for me:

CREATE FUNCTION fn_tvf(@input INT)
RETURNS TABLE
AS
        RETURN
        (
        SELECT  @input AS id
        UNION ALL
        SELECT  @input + 1
        )

GO

;WITH    q AS
        (
        SELECT  1 AS id
        ),
        t AS
        (
        SELECT  3 AS id
        )
SELECT  *
FROM    q
CROSS JOIN
        t
WHERE   t.id NOT IN (SELECT  * FROM fn_tvf(q.id))

What is exact error message you get?

Answer 3

Ok. I created an additional funciton that takes two args, one to create the table of related acnt_ids and another to look for in the resulting table. With this new function I could reformulate the query like so

SELECT COUNT(r.reg_id)
FROM registrations r 
JOIN profiles p ON (r.reg_frn_pro_id = p.pro_id)  
JOIN events e ON (r.reg_frn_evt_id = e.evt_id)  
WHERE dbo.func_checkAcntRelation(p.pro_frn_acnt_id, e.evt_frn_acnt_id) = 0