Help with SQL query (list strings and count in same query)
- by Mestika
Hi everybody,
I’m working on a small kind of log system to a webpage, and I’m having some difficulties with a query I want to do multiple things. I have tried to do some nested / subqueries but can’t seem to get it right.
I’ve two tables:
User = {userid: int, username}
Registered = {userid: int, favoriteid: int}
What I need is a query to list all the userid’s and the usernames of each user. In addition, I also need to count the total number of favoriteid’s the user is registered with.
A user who is not registered for any favorite must also be listed, but with the favorite count shown as zero.
I hope that I have explained my request probably but otherwise please write back so I can elaborate.
By the way, the query I’ve tried with look like this:
SELECT user.userid, user.username FROM user,registered WHERE user.userid = registered.userid(SELECT COUNT(favoriteid) FROM registered)
However, it doesn’t do the trick, unfortunately
Kind regards
Mestika
