Solved

Multiple subsets not working

Posted on 2014-01-14
4
252 Views
Last Modified: 2014-01-14
I have the following stored procedure

SELECT DISTINCT 
                         Client.ClientId, Client.ClientRef, Client.CompanyName, Contact.FirstName + ' ' + Contact.LastName AS name, ContactInfo.contactDetail AS email, 
                         ContactInfo_1.contactDetail AS tel, Contact.IsMailContact
FROM            Client INNER JOIN
                         Site ON Client.ClientId = Site.Clientid INNER JOIN
                         Contact ON Site.SiteId = Contact.SiteId LEFT OUTER JOIN
                         ContactInfo AS ContactInfo_1 ON Contact.ContactId = ContactInfo_1.Contactid AND ContactInfo_1.contactType = N'Main Phone' LEFT OUTER JOIN
                         ContactInfo ON Contact.ContactId = ContactInfo.Contactid AND ContactInfo.contactType = N'Email'
WHERE        (Client.ClientId IN
                             (SELECT        ClientId
                               FROM            ClientNotes
                               WHERE        (Type = 'Hot'AND 
							   (clientnotes.clientid in (SELECT        ClientId
                               FROM            ClientNotes
                               WHERE  Type <> 'Dead')) ))) AND (Contact.IsMailContact = 1)
ORDER BY  Client.CompanyName 

Open in new window


the clientnotes table  contains a mixture of types as shown here:

id     Clientid  NoteTitle Followupdate Type
35      63      test      NULL      General
36      79      Release Docs      23/01/2014      General
37      79      Release Docs      22/02/2014      Hot
38      63      Call back 130114      10/01/2014      Hot
39      63      The client has assured me that they want to go ahe      ad 12/01/2014      Hot
40    63    Client not returning calls 14/01/2014 Dead

I want to select only the clients where type = Hot BUT not those that are now Dead, so with the data shown I should only get clientid 79 as clientid 63 is both Hot and Dead.

Help I keep getting the Dead clientid's as well whats wrong with my storedprocedure?

Mark
0
Comment
Question by:markej
  • 2
  • 2
4 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39778943
Looking at that it seems the client is in two records.  One with Type=Hot and the other with Type=Dead.  Is that correct?  (If it is correct then is that allowed?)
0
 

Author Comment

by:markej
ID: 39778967
Yes that's allowed as the clientnotes table is used to record the contacts with the client, the type column is used to indicate the client contact ie General call, Need to callback, Hot indicates they are almost ready to sign and Dead which indicates no further contact required as they are not interested,

I need to show just the clients who don't have a Dead recorded against them BUT do have a Hot.
0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 39778971
WHERE        (Type = 'Hot'AND
                                             (clientnotes.clientid in (SELECT        ClientId
                               FROM            ClientNotes
                               WHERE  Type <> 'Dead'))

should be something like
                              WHERE        (Type = 'Hot'AND
                                             (clientnotes.clientid NOT in (SELECT        ClientId
                               FROM            ClientNotes
                               WHERE  Type = 'Dead'))
0
 

Author Closing Comment

by:markej
ID: 39779042
Great that works, thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question