Struggling with queries from two dissimilar tables

[gray8110]

I'm adding a new feature to our user results page that will add a link for each user WHEN they have an article/post that they authored. The articles (posts) are in a database that is distinct from the users (users) table. However, the posts db will list the author's user ID in posts.userID - in other words, if a user has a post, posts.userID and users.id will match.

My existing users query is pretty simple

(SELECT * FROM users ORDER BY lastname)

I'm struggling to be able to associate the posts.userID and users.id. I've tried a few methods and am not getting very far.

If I join the tables where the two fields match, it eliminates all users with no article tied to their ID. Additionally, I get one user result for every post they've written. If Bob has written 3 articles I get his name 3 times.

(SELECT * FROM users JOIN posts WHERE (users.id = posts.userID) ORDER BY lastname ASC)

I've also tried joining them without the WHERE constraint - I can filter the match in PHP later - This gives me every user in the DB one time for every post in the DB. If there are 100 posts total, I get 100 posts for EACH user.

What am I doing wrong here??

Thanks

[tomhath]

I'm not sure I follow what you're trying to do. You want one row for each user, but then what from the posts table to go along with that row? Just an indicator that there are posts for that id? Maybe a count of the posts?

You might need to change what's in the COUNT(), I didn't test this:

SELECT users.id, users.lastname, users.firstname, 
         count(posts.*) as num_posts
FROM users 
LEFT JOIN posts 
  ON (users.id = posts.userID) 
GROUP BY users.id, users.lastname, users.firstname
ORDER BY lastname ASC