Help figuring out a widget to display active tickets by admin

[tnetkevin]

Hi all, I'm hoping someone can help me figure this out.

 

We're trying to create a widget to display all tickets by admin. We're using, as a template, the "tickets_overview" widget which has a "tickets by status" and "tickets by department" chart built in.

 

So, everything about this widget works, except the query. We're fairly certain the query is the thing that's not working, since when we replace it with a known-working query, the widget does work properly.

 

Again, the goal of this query is to get a count of all active tickets BY admin to whom it's assigned. Can anyone tell me why it might not be working?

 

$query = "SELECT tbladmins.username,(SELECT COUNT(*) FROM tbltickets WHERE tbltickets.admin=tbladmins.username) FROM tblticketstatuses WHERE showactive=1 ORDER BY sortorder ASC";

 

 

Thanks for any help!!

[brian!]

have you checked to see that the values in tbladmins.username match tbltickets.admin in your database? they don't in ours!

 

tbladmins.username will be their login, but tbltickets.admin will be their full name, which I assume is taken from tbladmins.firstname and tbladmins.lastname

[tnetkevin]

Thanks, yes, we noticed this as well. As a test, we changed one of the usernames to match the admin (full name) exactly. We expected it to return the data for just that admin, but it still did nothing.