Help with a Query

  • (2 Pages)
  • +
  • 1
  • 2

17 Replies - 977 Views - Last Post: 05 March 2011 - 06:24 AM Rate Topic: -----

#1 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Help with a Query

Posted 04 March 2011 - 09:57 AM

OK To Start off, I have two tables here.

Customers and Notifications

Customers consists of
ListID (pkey)
CustomerName

Notifications consist of
NotificationID
CustomerID (fkey)
NotificationText
EntryTS (DateTime)
Deleted (True/False)

Fields are Text unless otherwise noted (not that I think it matters with this query

Anyway. I have the following SELECT statement that works.

SELECT Customers.CustomerName, Customers.ListID, Count(Notifications.NotificationText) AS TotalMsg
FROM Customers
  LEFT JOIN Notifications ON
            Customers.ListID = Notifications.CustomerID 
GROUP BY Customers.CustomerName, Customers.ListID;


This produces all customers and displays their total message counts whether it's 0 or not. However here is my problem.
I don't want to count anything where Deleted = True in the Notifications table.

I thought I could just add a WHERE Deleted = False to the above query but that only returns customers who have at least one row in the Notifications table.

I then thought I could just add the Deleted column to the list of Fields and to the Group By but I'm right back to only customers that have rows in the Notification table show up. Now I feel like I may need to do a sub-select of the notifications table without WHERE Deleted = False but I can't seem to get it to work
Here is what I've tried and I won't pretend that I even fully understand what I'm doing with it but it produces the same results as the original Query above.
SELECT Customers.CustomerName, Customers.ListID, Count(Notifications.NotificationText) AS TotalMsg
FROM Customers
  LEFT JOIN Notifications ON
            Customers.ListID = Notifications.CustomerID
  WHERE EXISTS (SELECT * FROM Notifications WHERE Deleted = False)
GROUP BY Customers.CustomerName, Customers.ListID


Any help is greatly appreciated.

This post has been edited by CharlieMay: 04 March 2011 - 09:58 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Help with a Query

#2 VolcomMky  Icon User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Help with a Query

Posted 04 March 2011 - 10:43 AM

Something along these lines

SELECT Customers.CustomerName, Customers.ListID, Count(Notifications.NotificationText) AS TotalMsg
FROM Customers
  LEFT JOIN Notifications ON
            Customers.ListID = Notifications.CustomerID 
WHERE Notifications.Deleted = False
GROUP BY Customers.CustomerName, Customers.ListID;


Or you can use a subquery
SELECT Customers.CustomerName, Customers.ListID, Count(Notes.NotificationText) AS TotalMsg
FROM Customers
INNER JOIN (SELECT * FROM Notifications WHERE Deleted = False) AS Notes
ON Customers.ListID = Notes.CustomerID 
GROUP BY Customers.CustomerName, Customers.ListID;


I am still working on my Sql Freehand so I wont guarantee this will work, but looks good to me lol

This post has been edited by VolcomMky: 04 March 2011 - 10:52 AM

Was This Post Helpful? 0
  • +
  • -

#3 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 10:48 AM

See if this works:
SELECT Customers.CustomerName, Customers.ListID, 'TotalMsg' = IsNULL(sub.TotalMsg, 0) 
FROM Customers
  LEFT JOIN (
        SELECT n.CustomerID, Count(n.NotificationText) AS TotalMsg
        FROM Notifications n
        WHERE n.Deleted = False
) sub ON Customers.ListID = sub.CustomerID 
GROUP BY Customers.CustomerName, Customers.ListID;


Was This Post Helpful? 2
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Help with a Query

Posted 04 March 2011 - 11:26 AM

@VolcomMky Both produced the same result, only customers with rows in the notifications table showed up.
@keakTheGEEK I'm getting "You tried to execute a query that does not include the specified expression 'CustomerID' as part of an aggregate function." So I changed it to this
SELECT Customers.CustomerName, Customers.ListID, 'TotalMsg' = IsNULL(sub.TotalMsg)
FROM Customers
    LEFT JOIN (SELECT n.CustomerID, Count(n.NotificationText) AS TotalMsg 
    FROM Notifications AS n  
    WHERE n.Deleted=False
GROUP BY n.CustomerID)  AS sub ON Customers.ListID=sub.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID;
(Notice Group By Clause added to FROM clause) I then received the error:
"You tried to execute a query that does not include the specified expression "'TotalMsg'=IsNULL(sub.TotalMsg)' as part of an aggregate function.
So then I changed it to this:
SELECT Customers.CustomerName, Customers.ListID, 'TotalMsg' = IsNULL(sub.TotalMsg)
FROM Customers
    LEFT JOIN (SELECT n.CustomerID, Count(n.NotificationText) AS TotalMsg 
    FROM Notifications AS n  
    WHERE n.Deleted=False
GROUP BY n.CustomerID)  AS sub ON Customers.ListID=sub.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID, TotalMsg

(notice TotalMsg added to 2nd Group By Clause)
This now lists all customers but the TotalMsg is 0 for all. ( I should note: It returns CustomerName, ListID and Expr1002) but they're all 0

This post has been edited by CharlieMay: 04 March 2011 - 11:29 AM

Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Help with a Query

Posted 04 March 2011 - 11:40 AM

OK, I think I have it. I need to check the raw data but this looks correct now. Here is the changes I made from your query
SELECT Customers.CustomerName, Customers.ListID, count(n.NotificationText) as TotalMsg
FROM Customers  LEFT JOIN (SELECT n.CustomerID, n.NotificationText
    FROM Notifications AS n  
    WHERE n.Deleted=False
)  AS sub ON Customers.ListID = sub.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID



OK, works perfect. Couldn't have gotten this far without your initial query to help me along.

Thanks keak :)

This post has been edited by CharlieMay: 04 March 2011 - 11:46 AM

Was This Post Helpful? 2
  • +
  • -

#6 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 11:47 AM

View PostCharlieMay, on 04 March 2011 - 11:40 AM, said:

OK, I think I have it. I need to check the raw data but this looks correct now. Here is the changes I made from your query
SELECT Customers.CustomerName, Customers.ListID, count(n.NotificationText) as TotalMsg
FROM Customers  LEFT JOIN (SELECT n.CustomerID, n.NotificationText
    FROM Notifications AS n  
    WHERE n.Deleted=False
)  AS sub ON Customers.ListID = sub.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID



You may want to do IsNULL(Count(n.NotificationText), 0) to get your 0's when null is returned...

That looks right. The count needed to be aggregated on the result of the sub query where as I was doing it within the sub query. Sorry, I was winging it but looks like you got the idea. ;)

This post has been edited by keakTheGEEK: 04 March 2011 - 11:48 AM

Was This Post Helpful? 0
  • +
  • -

#7 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Help with a Query

Posted 04 March 2011 - 11:50 AM

I think one of your problems was with your ISNULL needed to have what to do if it was null such as ISNULL(field, 0) will return a 0 if the field is null.
Was This Post Helpful? 0
  • +
  • -

#8 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 11:50 AM

View PostCharlieMay, on 04 March 2011 - 11:40 AM, said:

OK, I think I have it. I need to check the raw data but this looks correct now. Here is the changes I made from your query
SELECT Customers.CustomerName, Customers.ListID, count(n.NotificationText) as TotalMsg
FROM Customers  LEFT JOIN (SELECT n.CustomerID, n.NotificationText
    FROM Notifications AS n  
    WHERE n.Deleted=False
)  AS sub ON Customers.ListID = sub.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID



OK, works perfect. Couldn't have gotten this far without your initial query to help me along.

Thanks keak :)


No problem, and thanks for sharing your final result :)
Was This Post Helpful? 0
  • +
  • -

#9 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Help with a Query

Posted 04 March 2011 - 11:51 AM

it may be better to do the COUNT(ISNULL(n.NotificationText, 0))
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Help with a Query

Posted 04 March 2011 - 11:59 AM

OK, I probably should have mentioned this is an Access file ISNULL won't let me put any extra parameters.
Wrong number of arguments use with function in query expression count(ISNULL(n.NotificationText,0))

I even tried
ISNull(Count(n.NotificationText),0) 'same error
Was This Post Helpful? 0
  • +
  • -

#11 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 12:10 PM

Try COALESCE(Count(n.NotificationText), 0)

COALESCE is ANSI standard. If Access doesn't support that function I will be deeply disappointed :(

However, if that doesn't work you could always do it in your application code when you read that data... (There I go again making assumptions :))
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Help with a Query

Posted 04 March 2011 - 12:22 PM

OK I'll give it a shot. Now... I've come up with an additional problem. I want to do the same thing with the DEARegistry Table so I thought I could just add another left join like I would with a simple 3 table join query. But I keep getting Missing Operator.
SELECT Customers.CustomerName, Customers.ListID, count(n.NotificationText) AS TotalMsg, count(d.DEANumber) AS TotalDEA
FROM Customers LEFT JOIN (SELECT n.CustomerID, n.NotificationText
    FROM Notifications AS n  
    WHERE n.Deleted=False
)  AS sub ON Customers.ListID = sub.CustomerID 
LEFT JOIN (SELECT d.CustomerID, d.DEANumber
    FROM DEARegistry AS d
    WHERE d.isDeleted=False 
) AS dea ON Customers.ListID = dea.CustomerID
GROUP BY Customers.CustomerName, Customers.ListID;

Am I even going about it the right way with another table joined for the same type of result? Again, I can get this to work as long as I don't try to leave out rows marked as deleted. And yes, for some reason, the column is called isDeleted in the DEARegistry. Talk about consistancy :( I think I'll eventually port this to SQL Server and fix these but for now I just want to get the code working.
Was This Post Helpful? 0
  • +
  • -

#13 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 12:42 PM

@CharlieMay,


I think the problem is in the top select: count(d.DEANumber)

shouldn't that be dea.DEANumber...?
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Help with a Query

Posted 04 March 2011 - 12:51 PM

Still Missing Operator. I guess I was just going off of the initial single table join and trying to duplicate that for the second table join. There must be something between the two joins but I don't have a clue what it is. And I'm not finding a whole lot online with this scenerio.
Still looking though :D
Was This Post Helpful? 0
  • +
  • -

#15 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Help with a Query

Posted 04 March 2011 - 12:55 PM

View PostCharlieMay, on 04 March 2011 - 12:51 PM, said:

Still Missing Operator. I guess I was just going off of the initial single table join and trying to duplicate that for the second table join. There must be something between the two joins but I don't have a clue what it is. And I'm not finding a whole lot online with this scenerio.
Still looking though :D



Same deal with COUNT(n.NotificationText) also. That's part of the sub query sub, so it should be COUNT(sub.NotificationText)...


Try changing that one too and see if you still get the error. Good Luck :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2