Struggling with queries from two dissimilar tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gray8110
Forum Newbie
Posts: 3
Joined: Thu Jan 15, 2009 4:52 pm

Struggling with queries from two dissimilar tables

Post 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
watson516
Forum Contributor
Posts: 198
Joined: Mon Mar 20, 2006 9:19 pm
Location: Hamilton, Ontario

Re: Struggling with queries from two dissimilar tables

Post by watson516 »

Last edited by watson516 on Thu Jan 15, 2009 5:11 pm, edited 1 time in total.
gabriel1836
Forum Newbie
Posts: 16
Joined: Sat Jan 03, 2009 12:45 am
Location: Logan, UT

Re: Struggling with queries from two dissimilar tables

Post 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.
gray8110
Forum Newbie
Posts: 3
Joined: Thu Jan 15, 2009 4:52 pm

Re: Struggling with queries from two dissimilar tables

Post 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.
gabriel1836
Forum Newbie
Posts: 16
Joined: Sat Jan 03, 2009 12:45 am
Location: Logan, UT

Re: Struggling with queries from two dissimilar tables

Post 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.
gray8110
Forum Newbie
Posts: 3
Joined: Thu Jan 15, 2009 4:52 pm

Re: Struggling with queries from two dissimilar tables

Post 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.
Post Reply