Solved

Multiple subsets not working

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 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