Solved

Records included in results

Posted on 2014-02-05
11
231 Views
Last Modified: 2014-02-08
Hi,

Have a view as follows:

SELECT        dbo.ContactList.ContactListID, dbo.ContactList.ContactList, ISNULL(dbo.ContactList.Description, '-') AS Description, COUNT(dbo.ContactListContact.ContactID) AS ContactCount, dbo.ContactList.IsDeleted, 
                         dbo.ContactList.MailChimpListID
FROM            dbo.ContactList LEFT OUTER JOIN
                         dbo.ContactListContact ON dbo.ContactListContact.ContactListID = dbo.ContactList.ContactListID LEFT OUTER JOIN
                         dbo.Contact ON dbo.ContactListContact.ContactID = dbo.Contact.ContactID
WHERE        (dbo.ContactList.IsDeleted = 0) AND (dbo.ContactListContact.IsDeleted = 0) AND (dbo.Contact.IsDeleted = 0)
GROUP BY dbo.ContactList.ContactListID, dbo.ContactList.ContactList, dbo.ContactList.Description, dbo.ContactList.IsDeleted, dbo.ContactList.MailChimpListID

Open in new window


The WHERE clause is to ensure that I only get valid records into my list of contactlists. THis view is essentially returning a list of contactlists with the totals of members in each. The only issue with it is that if a contact list exists that doesn't have any members then it isn't included in results.

Schema is as follows:
Contact is a table of contacts
ContactList is a table of contactlists
ContactListContact is a junction table that marries the 2.
ContactID is a FK in ContactListContact
ContactListID is a FK in ContactListContact

How can I ensure that lists get included in the results even if they have no members.

Thanks.
0
Comment
Question by:tomfogarty
  • 6
  • 5
11 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39837593
You need to move (dbo.Contact.IsDeleted = 0) and (dbo.ContactListContact.IsDeleted = 0) from WHERE clause to the relevant ON clauses:
SELECT        dbo.ContactList.ContactListID, dbo.ContactList.ContactList, ISNULL(dbo.ContactList.Description, '-') AS Description, COUNT(dbo.ContactListContact.ContactID) AS ContactCount, dbo.ContactList.IsDeleted, 
                         dbo.ContactList.MailChimpListID
FROM            dbo.ContactList LEFT OUTER JOIN
                         dbo.ContactListContact ON dbo.ContactListContact.ContactListID = dbo.ContactList.ContactListID AND (dbo.ContactListContact.IsDeleted = 0) 
LEFT OUTER JOIN
                         dbo.Contact ON dbo.ContactListContact.ContactID = dbo.Contact.ContactID AND (dbo.Contact.IsDeleted = 0)
WHERE        (dbo.ContactList.IsDeleted = 0) 
GROUP BY dbo.ContactList.ContactListID, dbo.ContactList.ContactList, dbo.ContactList.Description, dbo.ContactList.IsDeleted, dbo.ContactList.MailChimpListID

Open in new window

0
 

Author Comment

by:tomfogarty
ID: 39838576
Hi,

For different purposes, I'm using a different query elsewhere to achieve similar results. The query above is to give back a table of lists and the number of members on each. The user can then click on a button and export the member information into Excel. However, the 2 sets of results are different and it seems to be to do with the IsDeleted filter. The other query (call it Excel Query) is:

Excel Query:
select count(distinct contactID)
from contact
where 
contactid in (select contactid from contactlistcontact where contactlistcontact.isdeleted = 0  AND contactlistid = '1f1c0774-2d1e-4650-8372-b1f2e14c185c')
and contact.IsDeleted = 0

Open in new window


This gives me a list of contacts for the list with the above ID. The figure returned for the Excel query is 467. The figure returned for the original query (call it Table Query) is 499. If I remove the contact.IsDeleted WhereClause in the Excel Query the figure is 499.

That tells me that the join in the Table Query above isn't being taken into account:
LEFT OUTER JOIN
                         dbo.Contact ON dbo.ContactListContact.ContactID = dbo.Contact.ContactID AND (dbo.Contact.IsDeleted = 0)

Open in new window


What do you think?
0
 
LVL 24

Expert Comment

by:chaau
ID: 39838628
If you want that the data from contacts table drive the results, move it to the left of the outer joins. The query I have provided will give you the count of ContactList records
0
 
LVL 24

Expert Comment

by:chaau
ID: 39838634
If you want to see only valid contacts, add "and Contact.ContactID is not null) to the end of the where clause of the query I have provided
0
 

Author Comment

by:tomfogarty
ID: 39838759
What difference does "and Contact.ContactID is not null)" do to the query if the issue may be related to the check for IsDeleted? How will it impact on the query.

The original query is to determine a list of contact lists and the number of members associated with it, based on a count of ContactListContact records. It works fine.

The second query is to get the data for the list of members attached to a particular list. As you can see from the "Excel Query" I'm getting a difference based on the fact that some of the Contact records related to the Contact Lists via ContactListContact are deleted but the original query doesn't seem to be filtering those records out properly.

I'm basing it on the figures I gave you. Do you see my point?

Thanks.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 24

Expert Comment

by:chaau
ID: 39839856
Didn't you ask that you want to see all contact lists, even those that are without the members? When you move the condition to the ON clause, the records from contacts table will not be retrieved, but replaced with NULL values. If you do not like to see the condition for NULL im the where clause, replace LEFT JOIN with the INNER JOIN. This will make the reports match. But what's your original question though?
0
 

Author Comment

by:tomfogarty
ID: 39840556
Originally, the query I showed you wasn't returning lists that had no members attached but it did accurately reflect deleted records IsDeleted = 0.

Your query works in terms of returning the right lists but it doesn't accurately reflect the deleted contacts (Contact.IsDeleted = 0).

That's where it is at the moment. Nearly there but the numbers obviously have to be right.

Thanks.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39840571
Can you please post some sample data with a few examples. It does not need to be big, just a few records
0
 

Author Comment

by:tomfogarty
ID: 39842845
I think this data will illustrate.

With this data, your query will return:
List1,4
List2,5
List3,3
List4,0
The list with no contacts are returned but it doesn't take into account deleted contacts (contact3)

The Excel query I use will return a list of contacts and takes into account deleted contacts but doesn't show lists that have no members. So the totals returned would be:
List1,3
List2,4
List3,2

Data:
Contact            
ID      Name      IsDeleted
1      Contact1      0
2      Contact2      0
3      Contact3      1
4      Contact4      0
5      Contact5      0
            
            
ContactLists            
ID      Name      IsDeleted
1      List1      0
2      List2      0
3      List3      0
4      List4      0

ContactListContact            
ID      ContactID      ContactListID
1      1            1
2      1            2
3      1            3
4      2            2
5      2            3
6      3            1
7      3            3
8      4            1
9      5            1
10      3            2
11      4            2
12      5            2
0
 
LVL 24

Accepted Solution

by:
chaau earned 350 total points
ID: 39843203
I guess, to return the count you add COUNT(*) to your statement. It is wrong, as it is demonstrated by this SQL Fiddle. It contains two queries:
SELECT dbo.ContactLists.ID, ContactLists.IsDeleted, dbo.ContactLists.Name,
COUNT(*)
FROM            dbo.ContactLists
LEFT OUTER JOIN dbo.ContactListContact 
  ON dbo.ContactListContact.ContactListID = dbo.ContactLists.ID AND (dbo.ContactListContact.IsDeleted = 0) 
LEFT OUTER JOIN dbo.Contact 
  ON dbo.ContactListContact.ContactID = dbo.Contact.ID AND (dbo.Contact.IsDeleted = 0)
WHERE (dbo.ContactLists.IsDeleted = 0) 
GROUP BY dbo.ContactLists.ID, ContactLists.IsDeleted, dbo.ContactLists.Name;

Open in new window

                                           
and
SELECT dbo.ContactLists.ID, ContactLists.IsDeleted, dbo.ContactLists.Name,
COUNT(Contact.ID)
FROM            dbo.ContactLists
LEFT OUTER JOIN dbo.ContactListContact 
  ON dbo.ContactListContact.ContactListID = dbo.ContactLists.ID AND (dbo.ContactListContact.IsDeleted = 0) 
LEFT OUTER JOIN dbo.Contact 
  ON dbo.ContactListContact.ContactID = dbo.Contact.ID AND (dbo.Contact.IsDeleted = 0)
WHERE (dbo.ContactLists.IsDeleted = 0) 
GROUP BY dbo.ContactLists.ID, ContactLists.IsDeleted, dbo.ContactLists.Name

Open in new window

Please have a look at the result. The first one does return the numbers you have observed. However, the second query that counts (Contact.ID) will return only the number of NOT Deleted contacts.

In order to figure this out better I recommend that you remove the GROUP BY and select all columns from the query with all joins. You will see that where the contact is deleted there will be NULLs:
SELECT *
FROM            dbo.ContactLists
LEFT OUTER JOIN dbo.ContactListContact 
  ON dbo.ContactListContact.ContactListID = dbo.ContactLists.ID AND (dbo.ContactListContact.IsDeleted = 0) 
LEFT OUTER JOIN dbo.Contact 
  ON dbo.ContactListContact.ContactID = dbo.Contact.ID AND (dbo.Contact.IsDeleted = 0)
WHERE (dbo.ContactLists.IsDeleted = 0) 
ORDER BY dbo.ContactLists.ID, dbo.ContactListContact.ContactID;

Open in new window

                                           
I think this explains
0
 

Author Closing Comment

by:tomfogarty
ID: 39843996
OK.

Looks like the second query above works. When I count the Contact.ContactID as opposed to ContactListContact.ContactID. I'm checking the numbers against the live data and it looks good.

Thanks. I'll close it off. Good work.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

12 Experts available now in Live!

Get 1:1 Help Now