Counting rows with multiple criteria?

[Noozer]

Consider the following two queries:

1) SELECT COUNT(*) AS BinTickets FROM Ticket GROUP BY Bin;

2) SELECT BinKeyLink FROM User2Bin WHERE UserKeyLink=User;

The first query counts the the number of tickets for each type of Bin. Each
row is the total number of tickets for a Bin.

The second query lists all the Bins for a specific User. Each row specifies
a Bin that the user is allowed to access.

What I want to do is find the total number of tickets that the user has
access to.

Filter the first query so that it will only Count tickets where the Bin is
linked to the user as shown in the second query and then sum all the Counts
to return a single sum.

Any help here? What kind of query is this? I'm familiar with Joins and
Unions, but it makes my head spin trying to put it into SQL.

If it matters, I'm using an MS Access table. Will move to MySQL once I have
the app functioning.

Thanks!!!

 

[Wolfgang Kais]

Nozer.

Consider the following two queries:
1) SELECT COUNT(*) AS BinTickets FROM Ticket GROUP BY Bin;
2) SELECT BinKeyLink FROM User2Bin WHERE UserKeyLink=User;

The first query counts the the number of tickets for each type of Bin.
Each row is the total number of tickets for a Bin.

The second query lists all the Bins for a specific User.
Each row specifies a Bin that the user is allowed to access.

What I want to do is find the total number of tickets that the user has
access to.

Filter the first query so that it will only Count tickets where the Bin
is linked to the user as shown in the second query and then sum
all the Counts to return a single sum.
[...]

Select Count(*)
From Ticket Inner Join User2Bin On Ticket.Bin = User2Bin.BinKeyLink
Where User2Bin.UserKeyLink=User

Or how about this:
Select User2Bin.UserKeyLink, Count(*)
From Ticket Inner Join User2Bin On Ticket.Bin = User2Bin.BinKeyLink
Group By User2Bin.UserKeyLink

 

[Noozer]

I bow down to you... SQL Guru!

Thank you very much!

 

[Noozer]

Or how about this:
Select User2Bin.UserKeyLink, Count(*)
From Ticket Inner Join User2Bin On Ticket.Bin = User2Bin.BinKeyLink
Group By User2Bin.UserKeyLink

The above query lists each user and the count of tickets. Assuming I also
had a table named "Users" with columnw named "UserKey" and "FullName" how
could I change the above query to show the users name instead of their index
key?

Thanks... it's really appreciated!

 

[Wolfgang Kais]

Noozer.

The above query lists each user and the count of tickets.
Assuming I also had a table named "Users" with columnw
named "UserKey" and "FullName" how could I change the above
query to show the users name instead of their index key?

The following will work in Access, I don't know how about MySQL:
Select Users.FullName, Count(*) As TicketCount
From (Users Inner Join User2Bin On Users.UserKey = User2Bin.BinKeyLink)
Inner Join Ticket ON User2Bin.UserKeyLink = Ticket.Bin
Group By Users.FullName, Users.UserKey;

If the FullName column contained unique values, you only needed
to group by Users.FullName.