March 2, 2016 at 8:08 am
Hi,
I know this may sound like a very basic query, but I can't seem to get it to work. I have tried several different
I have a member table and a claim table. I want to find members that have 2 records in the claim table and at least one record in membercoverage that has a coveragestatusid = 3.
This is what I have that seems to work
select top 1 member_no, count(c.patient_id) from claim c
join member m on m.member_id = c.patient_id
join membercoverage mc on mc.memberid = m.member_id and coveragestatusid = 3
group by m.member_no
having count(patient_id) = 2
I don't want to include the count in what is returned from the query, but if I remove the count from the select statement, then this query doesn't work.
Any help you can provide would be greatly appreciated
Christine
March 2, 2016 at 8:50 am
It should work if you remove the COUNT from the SELECT list. If it doesn't, then perhaps post your code (plus a full repro script - i.e. CREATE TABLE statements and INSERT statements so that we can recreate the issue without having to guess).
However, if the requirement is just to find rows that have (at least one) matching row in SubTable1 and (exactly) two matching rows in SubTable2, then I would use this pattern:
WHERE EXISTS (SELECT * FROM correlated subquery for SubTable1)
AND (SELECT COUNT(*) FROM correlated subquery for SubTable2) = 2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply