Solved

Records included in results

Posted on 2014-02-05
11
234 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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