• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Multiple subsets not working

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
markej
Asked:
markej
  • 2
  • 2
1 Solution
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
markejAuthor Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
markejAuthor Commented:
Great that works, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now