Solved

Multiple subsets not working

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

17 Experts available now in Live!

Get 1:1 Help Now