Page 1 of 1
Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 4:54 pm
by 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
Code: Select all
(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.
Code: Select all
(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
Re: Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 5:10 pm
by watson516
Re: Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 5:10 pm
by gabriel1836
To fix your query, try the following:
Code: Select all
SELECT *
FROM users
LEFT OUTER JOIN posts ON users.id = posts.userID
ORDER BY lastname ASC
However, as you've already noticed this will return a record for each post that a user has made so you'll be getting a lot of extra data. Therefore I would suggest that you handle this by returning some sort of collection of posts based on the displayed user's id.
Re: Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 5:53 pm
by gray8110
gabriel1836 wrote:However, as you've already noticed this will return a record for each post that a user has made so you'll be getting a lot of extra data. Therefore I would suggest that you handle this by returning some sort of collection of posts based on the displayed user's id.
Can you elaborate on this? Your query eliminated the duplication for users without posts which is a step forward, but I still only want to display an one result per user. You're absolutely correct there. I assume I can do something with the my_sql_fetch_array that will only return the first result when users.id and posts.userID match. Don't know where to begin though.
Re: Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 8:41 pm
by gabriel1836
Try the following to get a single record back for each user with their number of posts as a field:
SELECT users.id, COUNT(posts.userID)
FROM users
LEFT OUTER JOIN posts ON users.id = posts.userID
GROUP BY users.id
ORDER BY lastname ASC
Then you can simply check on the number of posts that are returned in the second field.
Re: Struggling with queries from two dissimilar tables
Posted: Thu Jan 15, 2009 8:47 pm
by gray8110
gabriel1836 wrote:Try the following to get a single record back for each user with their number of posts as a field:
SELECT users.id, COUNT(posts.userID)
FROM users
LEFT OUTER JOIN posts ON users.id = posts.userID
GROUP BY users.id
ORDER BY lastname ASC
Then you can simply check on the number of posts that are returned in the second field.
That will work perfectly. Thanks for the suggestion.