Btukfyl

I need a little help for an SQL query.

I have tables: clients | claims | statuses | contacts

I have a query to find all clients and their contact details where any of their claims is a specified status:

SELECT

  clients.id           AS "Client Ref"

 ,claims.clientclaimid AS "Claim Number"

 ,Contacts.PhoneHome   AS "Mobile"

 ,statuses.description AS Status

FROM

  dbo.claims

LEFT JOIN

  statuses

    ON

    dbo.claims.statusID = statuses.ID

LEFT JOIN

  clients

    ON

    dbo.claims.clientid = clients.id

LEFT JOIN

  contacts

    ON

    clients.contactid = Contacts.id

WHERE

  statuses.description = 'client - pack sent to customer'

  AND (DATEADD(MM, -@joinedpremonthsago, GETDATE()) > clients.DateJoined)

  AND clients.DateJoined > 01 / 01 / 2012

  AND claims.active = 1

ORDER BY

  [Client Ref]

 ,[Claim Number];

I now need this to only pull clients where ALL of their claims are in specified status, but I don't know how to do this. How can I get clients where all of the claims have this status description? Could I have guidance or solutions for this?

Here is relevant schema;
Claims Table

Contacts Table

Clients Table

Here is an image of the query returning where ANY of the client's claims are in status;
Current results

The solution is to use the principle of exclusion. You write a query to get all clients that do have the status at least once. Great news: that part is already done :) Next you write a query to find clients that have any other status. Once you have both queries, you put them together to exclude the 2nd set from the first. You can do this in several ways: a NOT EXISTS() expression, a NOT IN() expression, an exclusion join, or the EXCEPT keyword can all work.

Personally I'm most comfortable with exclusion joins, but NOT EXISTS() is more common and tends to perform a little better:

select cli.id as "Client Ref", cla.clientclaimid as "Claim Number", co.PhoneHome as "Mobile"



from dbo.claims cla



inner join statuses s on cla.statusID = s.ID 

inner join clients cli on cla.clientid = cli.id

left join contacts co on cli.contactid = co.id



where s.description = 'client - pack sent to customer'

    and (DateAdd(MM, -@joinedpremonthsago, GetDate()) >  cli.DateJoined) 

    and cli.DateJoined > 01/01/2012

    and cla.active=1



    and NOT EXISTS ( 

        select 1 

        from clients cli0

        inner join claims cla0 on cla0.clientid = cli0.id

        inner join statuses s0 on s0.ID = cla0.statusID

        WHERE cli0.ID = cli.ID

           AND s0.description <> 'client - pack sent to customer'

    )



order by [Client Ref], [Claim Number]

Exclusion join version:

select cli.id as "Client Ref", cla.clientclaimid as "Claim Number", co.PhoneHome as "Mobile"



from dbo.claims cla



inner join statuses s on cla.statusID = s.ID AND s.description = 'client - pack sent to customer'

inner join clients cli on cla.clientid = cli.id

left join contacts co on cli.contactid = co.id

-- the "JOIN" part of an exclusion join

left join statuses s2 on cla.statusID = s2.ID AND s2.description <> 'client - pack sent to customer'



where (DateAdd(MM, -@joinedpremonthsago, GetDate()) >  cli.DateJoined) 

    and cli.DateJoined > 01/01/2012

    and cla.active=1



    -- the "EXCLUSION" part of an exclusion join

    and s2.ID IS NULL



order by [Client Ref], [Claim Number]

Note how I chose inner rather then left for some of the original joins. The way fields from those tables were used in the WHERE clause already made them effectively inner joins. Being honest about the join type helps you spot bugs and may allow Sql Server to build a better execution plan.

Also note I removed the status from the SELECT clause results, as that is now implied by the requirements.

Finally, note how I added table aliases to the query. It's good practice to always use table aliases with your queries. It's absolutely necessary to avoid ambiguity if you want to reference the same table more than once in a single query, as we do in both examples here. By convention, these aliases are often short — even single letter — mnemonics for the table names. So cli in this query is short for client, and I used 3 whole characters so it can be distinguished from claims. cli0 is used in the inner query to mean "client prime"... think of it as if the 0 were a subscript.

Something like (totaly untested code!):

select  clients.id as "Client Ref", claims.clientclaimid as "Claim Number",

        Contacts.PhoneHome as "Mobile",statuses.description as Status

from dbo.claims

left join clients on dbo.claims.clientid = clients.id

left join contacts on clients.contactid = contacts.id



where (DateAdd(MM, -@joinedpremonthsago, GetDate()) >  clients.DateJoined) 

    and clients.DateJoined > 01/01/2012

    and claims.active=1

    and dbo.claims.clientID in (

       select dbo.claims.clientID 

       from dbo.claims

       left join statuses on dbo.claims.statusID = statuses.ID

       where statuses.description = 'client - pack sent to customer'

    )



order by [Client Ref], [Claim Number]

Should do the trick.