gray8110 Posted January 15, 2009 Share Posted January 15, 2009 I am doing a results page that needs to draw information from two dissimilar tables - a posts table and a users table. Both tables have an id row that corresponds with the user ID - I ned to know when those two fields match. I've tried selecting the rows from both tables - this actually got the results I was looking for, but each entry in the users table was repeated for each entry in the posts table. That query was similar to this: $result = ("SELECT users.id, users.name, posts.userID FROM users, posts", $connection); while ($row = mysql_fetch_array($result)) { There are more rows queries, but I don't want to waste space - needless to say, I only want one result for each user entry. Am I doing something blatantly wrong with this query that the while loop is looping through both tables or something? Any way to prevent this. I've also tried having a second query in and out of the while loop and filtering the results in a variety of ways, but can't seem to get at the userID from the posts table. Any suggestions? Thanks Quote Link to comment Share on other sites More sharing options...
RussellReal Posted January 15, 2009 Share Posted January 15, 2009 SELECT * FROM `table1` JOIN `table2` ON (table1.user_id = table2.user_id) WHERE table1.field = 'WHATEVER' AND table2.field = 'WHATEVER' Quote Link to comment Share on other sites More sharing options...
Cosizzle Posted January 15, 2009 Share Posted January 15, 2009 SELECT u.id, u.name, p.userID FROM users u INNER JOIN posts p ON (u.id = p.userID) Try that as your query Quote Link to comment Share on other sites More sharing options...
gray8110 Posted January 15, 2009 Author Share Posted January 15, 2009 SELECT u.id, u.name, p.userID FROM users u INNER JOIN posts p ON (u.id = p.userID) Try that as your query I tried this and two things happen. I get the user result for each corresponding post... in other words, each user with a post had their result appear once for each post. Additionally, the users with no posts were eliminated from the results (I still want their data) To clarify, this is page is effectively a list of all users, but I need to display a link to their posts if they have a post(s). The only thing I need to know is whether that userID appears in the userID field of any row in the posts table. $result = mysql_query("SELECT * FROM users JOIN posts ON (users.id = posts.userID) ORDER BY lastname ASC", $connection); Quote Link to comment Share on other sites More sharing options...
Cosizzle Posted January 16, 2009 Share Posted January 16, 2009 in your statement it looks like your using a JOIN, did you try it with a INNER JOIN? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.