Solved

Multiple subsets not working

Posted on 2014-01-14
4
245 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now