# Selecting three tables with relationship

I've been working on improving my website code. I've noticed that querying from a table inside a PHP while function, where while loop is also querying another table:

while($row = mysql_fetch_assoc($table1)){
$table2 = mysql_query(select); mysql_fetch_assoc($table2);
}


It might be slowing down the performance, depending on the fact that we're querying the second table for each row of the first table.

So I've decided to select both of the tables from the same query. I came up with this code to accomplish what I think is more sufficient.

Now I have few questions:

1. Am I correct for what I've said about the way my old code is slow?
2. Would using PHP functions to query from each table individually be more sufficient?
3. Is there anything that I need to add or change in my code, that I've made?

Code:

SELECT posts.*, users.username, SUM(IF(rates.hash = posts.hash, (rates.likes - rates.dislikes), 0)) AS rate
FROM posts,users,rates WHERE posts.position = posts.submitter AND users.username = posts.submitter GROUP BY posts.hash ORDER BY posts.ID DESC LIMIT 5

• Please include all of the code here. We cannot review code behind a link.
– Jamal
May 2 '14 at 7:43
• it's a demo link from sqlfiddle.com however, i've posted the code May 2 '14 at 7:47
• So are you working with two tables or three ? Also, can you give the PHP code that creates select ?
– Zoyd
May 2 '14 at 13:09

I don't really understand what this join is trying to accomplish? Seems redundant since the information is the same, but perhaps with your real world data it is not always the case.

WHERE posts.position = posts.submitter

Old-style JOINs are not recommended. Use INNER JOIN instead. This may also cause performance issues because rates is not explicitly joined so it would be trying to do a CROSS JOIN and those are very slow not to mention it can mess up your result set.

FROM    posts,
users,
rates
WHERE   posts.position = posts.submitter


Becomes:

SELECT  posts.*,
SUM(
IF(rates.hash = posts.hash, (rates.likes - rates.dislikes), 0)
) AS rate
FROM    posts