Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Records included in results

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
tomfogarty
Asked:
tomfogarty
  • 6
  • 5
1 Solution
 
chaauCommented:
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
 
tomfogartyAuthor Commented:
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
 
chaauCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
chaauCommented:
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
 
tomfogartyAuthor Commented:
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
 
chaauCommented:
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
 
tomfogartyAuthor Commented:
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
 
chaauCommented:
Can you please post some sample data with a few examples. It does not need to be big, just a few records
0
 
tomfogartyAuthor Commented:
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
 
chaauCommented:
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
 
tomfogartyAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now