SQL Query....

[cridley]

Here goes...

1st table: users
fields : userID, userName

2nd table : response
fields : respID, userID, verified

in users, each userID is unique, in response there are many respID entries per userID, some of which are verified (value 1), the rest of which are not (value 0).

I am looking for a query to return something in the following format (have tried using count and can't quite get to the desired result):

userID | username | total n#ofentries | verified=0 | verified=1
foruser
1 | bob | 5 | 2 | 3
2 | frank | 4 | 3 | 1
3 | John | 10 | 3 | 7

Where each user appears once, followed by total number of responses, followed by number of verified responses, followed by number of unverified responses.

How?

Thanks in advance,
C.

[cridley]

by the way, in mysql...

[mattyblah]

I'll give it a try:

SELECT u.userID, username, Count(*) AS `total n#ofentries for user`, Sum(CASE WHEN verified=0 THEN 1 ELSE 0 END) AS `verified=0`, Sum(CASE WHEN verified=1 THEN 1 ELSE 0 END) AS `verified=1` from users u INNER JOIN response r on u.userID = r.userID GROUP BY u.userID, username ORDER BY u.userID ASC

That might work, or be totally off. I haven't had much experience with MySQL, moreso with SQL Server, but I believe the two are pretty similar in regards to something like this.

[cridley]

Cheers, that's spot on, still learning when it comes to sql, and having to do it quick as it's a main part of my job. Will be using the same logic for quite a few queries.

One thing though, i've changed it to a LEFT JOIN and obviously the total number of entries comes up as one even when there is no entry in the right hand table. I want to display those as having 0 entries (as in user 'bob' has no entries in r table). I can easily do this part with a simple check whilst processing the results but it would be nicer if i didn't have to. Will try to figure this out on my own though.

Thanks again,
C.