count() causes query to be pickier...how to fix? 2

[pullingteeth]

I have the following tables (abbreviated):

User
----
username
firstname
lastname
role_code - describes the role of the user

Book_Event
----------
book_number
user
timestamp
event_code - describes the event

I wrote the following query to provide me with a list of users (within specific roles) who "have" books at a specific event (the fact that the book is at event 110 means that they "have" the book), but not beyond that event.

SELECT u1.firstname, u1.lastname, count(book_number)
FROM user AS u1 INNER JOIN book_Event ON u1.username = book_Event.user
WHERE ((book_Event.user) In 
    (SELECT u2.username
     FROM user as u2
     WHERE ((u2.role_code) Between 21 And 24)))
GROUP BY u1.firstname, u1.lastname
HAVING (MAX(event_code) = 110);

The query almost works. The problem is that as soon as a user has a book at a higher event, e.g. 120, they disappear from the results. This would seem to be a join related problem. However, the following slightly modified version(removing the count, and just listing the raw book_number) does not suffer from the same problem, leading me to think that the join is correct.

SELECT u1.firstname, u1.lastname, book_number
FROM user AS u1 INNER JOIN book_Event ON u1.username = book_Event.user
WHERE ((book_Event.user) In 
    (SELECT u2.username
     FROM user as u2
     WHERE ((u2.role_code) Between 21 And 24)))
GROUP BY u1.firstname, u1.lastname, book_number
HAVING (MAX(event_code) = 110);

I would also like to list all users who have zero books at the event, with a 0 next to their name; however, this is a much lower priority than fixing the above problem.

Thanks!

 

[PHV]

Have you tried this ?
SELECT u1.firstname, u1.lastname, count(book_number)
FROM user AS u1 INNER JOIN book_Event ON u1.username = book_Event.user
WHERE u1.role_code Between 21 And 24 And event_code>=110
GROUP BY u1.firstname, u1.lastname;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pullingteeth]

Thanks for your reply, PHV. Your modification prevents the problem, BUT it doesn't quite give me the data I need.

Books in the database move through a series of events, all of them ending up at between event 300 and 400. Therefore, using >=110 would mean that the numbers in the resultset from the query would rise indefinitely, which is not what I want.

What I'm looking for is the number of books at maximum event 110 for each of the specified users. That is, I don't care how many books they've put to e.g. event 120, and I don't care about the books that have reached any events higher than 110.

So, if my dataset for book_Event was:

book_number	user	book_event
1		joe	110
1		joe	120		
2		beck	110
3		joe	110
4		beck	90
4		beck	110
5		joe	90
6		joe	130
7		beck	110
7		beck	120
8		beck	110

... then the relevant books would be 2,3,4,8. The resultset should be:

beck : 3
joe : 1

Hope that's clear.

Thanks

 

This query gives a list of each user in the interesting role who has a book with an event_code of 110. They may have books a different levels, no matter, they are listed. But those who do not have a book at level 110 are not listed.

SELECT DISTINCT u1.firstname, u1.lastname
FROM user AS u1
JOIN book_Event e ON u1.username = e.user
WHERE u1.role_code Between 21 And 24
  AND e.event_code = 110;

This query shows how many books with event_code 110 each user has.

SELECT u1.firstname, u1.lastname, COUNT(*)
FROM user AS u1
JOIN book_Event e ON u1.username = e.user
WHERE u1.role_code Between 21 And 24
  AND e.event_code = 110;
GROUP BY u1.firstname, u1.lastname

Your requirements statements seem contradictory.
On the one hand you say
"... users ... who "have" books at a specific event ... 110 ... but not beyond that event."

On the other you say
"The problem is that as soon as a user has a book at a higher event, e.g. 120, they disappear from the results."


My queries give users with a book at 110, they may have books at other levels, too, that is OK they are still listed.

 

[PHV]

And this ?
SELECT U.firstname, U.lastname, Count(E.book_number)
FROM user As U INNER JOIN book_Event As E ON U.username = E.user
WHERE U.role_code Between 21 And 24 And E.event_code=110
And Not Exists(SELECT * FROM book_Event As B
WHERE B.book_number=E.book_number And B.user=E.user And B.event_code>110)
GROUP BY U.firstname, U.lastname;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

SELECT u1.firstname, u1.lastname,
(Select COUNT(*) from book_Event e
Where u1.username = e.user
Having Max(e.event_code = 110))
FROM user AS u1
WHERE u1.role_code Between 21 And 24

 

[pullingteeth]

(), nice, but I think you misunderstand my specs (not hard to do, since they aren't exactly clear.) The problem I was having was that neither Joe nor Beck was being displayed in the resultset, because of books 1 and 7.

PHV - "And Not Exists" is a new one on me. Thanks! A star for you!

JonFer - interesting. I think I tried doing something similar before, only to realize that Access 97 doesn't support the syntax. But I'll experiment.


I've been playing in the meantime, and came up with the following, which works:

SELECT u1.firstname, u1.lastname, Count(be1.book_number) AS CountOfBook_number
FROM user AS u1 LEFT JOIN book_Event AS be1 ON u1.username = be1.user
WHERE (((u1.username) In 
    (SELECT u2.username
     FROM user AS u2
     WHERE ((u2.role_code) Between 23 And 27))) 
AND (le1.loan_number IN  
    (SELECT be2.book_number 
     FROM book_event AS be2 
     GROUP BY be2.book_number 
     HAVING MAX(be2.event_code)= 110)))
GROUP BY u1.firstname, u1.lastname;

I'll try the suggestions, though, as they look neater.

Thanks, all.

 

[JonFer]

Access 97 does support sub-queries in the Select clause so maybe your syntax had an error.

 

[PHV]

And HAVING clause without GROUP BY ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

FYI, doing something like this:

Select User from table1
Where User In (Select User from table1 Where FieldName=1)

is much slower than this which returns the same results:

Select User from table1
Where FieldName=1

 

[JonFer]

PHV - Sure. You wouldn't use it as your main query but as a correlated sub-query. If you start with this and then restrict it to one User, there is no need for the Group By.

Select User, Count(*)
From Table1
Group By User
Having Max(BookCode)="110"

 

[pullingteeth]

JonFer, your code does in fact work in 97! Very nice to know.

However, there is one bug with it; I have a user who has a book following the pattern of 1 and 7 above; they show up, but their total (of other books) does not. Both my and PHV's versions catch that user's total correctly; however, neither of our queries show the users who don't have totals.

Any ideas?

 

[PHV]

show the users who don't have totals
Save the following query as, say, qryCountOfBook:
SELECT U.username, U.firstname, U.lastname, Count(E.book_number) As CountOfBook_number
FROM user As U INNER JOIN book_Event As E ON U.username = E.user
WHERE U.role_code Between 23 And 27 And E.event_code=110
And Not Exists(SELECT * FROM book_Event As B
WHERE B.book_number=E.book_number And B.user=E.user And B.event_code>110)
GROUP BY U.username, U.firstname, U.lastname
;
Then create a new one like this:
SELECT firstname, lastname, CountOfBook_number
FROM qryCountOfBook
UNION SELECT U.firstname, U.lastname, 0
FROM user As U LEFT JOIN qryCountOfBook As C ON U.username=C.username
WHERE C.username Is Null
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops, much simpler:
SELECT U.firstname, U.lastname, Nz(CountOfBook_number, 0) As NumberOfBooks
FROM user As U LEFT JOIN qryCountOfBook As C ON U.username=C.username
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

This may not work in Access 97 but try it out.

SELECT u1.firstname, u1.lastname, 
   (Select Count(*) from book_event e 
    where e.user=u1.user And e.event_code=110 and 
     e.book_num in (Select book_num from book_event 
      group by book_num having max(event_code)=110)) AS Books
FROM user as u1
Where u1.role_code between 21 and 24

My previous SQL counted all event records for users whose max event_code was 110 when looking at all of their books instead of looking at the max event_code for each book.