Select from multiple tables

[xylude]

Trying to get results from 5 tables where the userId is the same. I am getting like 8k rows for where there should be about 20-30 or so. Here is my statement:

mysql_query("SELECT * FROM userrating, postratings, blogCommentRatings, commentratings, gamecommentratings WHERE userrating.userId = '$this->userId' AND postratings.userId = '$this->userId' AND blogCommentRatings.userId = '$this->userId' AND commentratings.userId = '$this->userId' AND gamecommentratings.userId = '$this->userId'") or die(mysql_error());

I am getting results like a cross join where every combination of rows possible is coming back. I know there is some sort of JOIN that may help me can anyone give me some advice?

[drjohn]

Only the first condition should refer to the variable, the rest should really be along the lines of

AND tablename2.id=tablename1.id
AND tablename3.id=tablename2.id

etc

And you have to check your ERD to chain the tables in the correct order.

Although JOIN terminology is the preferred way to express things, WHERE will do exactly the same thing (although using the JOIN syntax makes some things easier to express)

Not sure why you're getting so many more rows, but for a cross join you'd get rows in tableA X rows in TableB X etc.

[shekar.bdn]

select emp_name,grade
from employee , grade
where salary between lowsal and higsal

[tyson.crouch]

your problem is that you are using a cross join which will return n^n results. You should use an inner (natural) join to return rows with all matching results.

SELECT *
FROM 
-- Join #1
(
        -- Join #2
	(userrating INNER JOIN postratings USING(userID) )

	INNER JOIN

        -- Join #3
	(blogCommentRatings INNER JOIN commentratings USING(userId) )

	USING(userID)

)
-- Join #4
INNER JOIN gamecommentratings 
USING(userId)

try using the above FROM clause to join all of your tables