[SOLVED] Queries from two dissimilar tables

[gray8110]

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

[RussellReal]

SELECT * FROM `table1`

JOIN `table2`

ON (table1.user_id = table2.user_id)

WHERE table1.field = 'WHATEVER'

AND table2.field = 'WHATEVER'

 

 

[Cosizzle]

SELECT 
u.id, 
u.name, 
p.userID 
FROM 
users u INNER JOIN posts p ON (u.id = p.userID)

 

Try that as your query

[gray8110]

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);

[Cosizzle]

in your statement it looks like your using a JOIN, did you try it with a INNER JOIN?