0

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?

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)
5
  • Oh no, well if I can only return one column, that defeats the purpose because the main query (the long one) returns a bunch of columns... and I need all of those columns... only I want to run that longer query only for items which occur >=3 times ... and Idea how I should be approaching this?
    – boog
    Jan 16 '20 at 21:25
  • No worries! It's only the query that you put in the IN(…) clause that can only return one column. Your main query can do as it pleases, of course.
    – fds
    Jan 16 '20 at 21:28
  • Think you could paste it together for me and post it as an answer? I tried to remove the count but it doesn't run, says that names_id needs a correlation name. Any idea what I'm doing wrong?
    – boog
    Jan 16 '20 at 21:35
  • You have a names_id in multiple places, try prefixing it so it knows which one interests you. For example, and names.names_id IN (
    – fds
    Jan 16 '20 at 21:37
  • Thank you, that did the trick. I can't give you credit for the answer unless you post an answer though.
    – boog
    Jan 16 '20 at 21:56
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.