Running a query on the results of another query?

Question

I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase

For example, the following query works to count the number of times/rows a each value shows up in the 'referred_link' column of the cases table, and show me only those that appear 3 or more times:

SELECT cases.referred_link, count(*)
FROM CASES
GROUP BY referred_link
HAVING COUNT(*) >= 3

The next query pulls a bunch of data from various tables and filters down on the required criteria. The catch is, I only want this 2nd query to run on the results from the first query... items which show up 3+ times on the referred_link table.

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='home_addr') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'

I tried something like this (doesn't work, of course):

select * from cases inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a
on names.names_id=a.names_id
left join party on cases.casenum=party.case_id
where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'
and names_id IN (SELECT cases.referred_link, count(*)
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)

Can anybody point me in the correct direction for how this could be accomplished?

Answer 1

When you put a subquery between IN(…), you have to ensure it returns exactly one column. Going by your other query, it seems cases.referred_link is actually already a names_id, so you are already pretty much there. All you need to do is remove that extra count(*) from the list of selected columns.

… and names.names_id IN (SELECT cases.referred_link
                FROM CASES
                GROUP BY referred_link
                HAVING COUNT(*) >= 3)

Answer 2

So what happen if you use a simple JOIN? The JOIN condition is most likely wrong because I don't know which columns link the 2 queries.

SELECT *
FROM (
    SELECT cases.referred_link, count(*)
    FROM CASES
    GROUP BY referred_link
    HAVING COUNT(*) >= 3
) cnt
JOIN (
    select * from cases inner join names on cases.referred_link=names.names_id
    left join (select * from mailing_list_defined where mailing_list='home_addr') a
    on names.names_id=a.names_id
    left join party on cases.casenum=party.case_id
    where names.person='Y' and date_opened>=today()-6 and party.our_client='Y' and party.role='Plaintiff'
) data ON data.names_id = cnt.referred_link