Supplement Content From Table2 With Table1

Would I use a JOIN command?

[itledi]

Hello,

I have a simple query to insert content to construct my web pages:

mysql_query("SELECT * FROM table1 ORDER BY date DESC");

In that table are titles, descriptions, links and a unique hash.

What I would like to do is to supplement this data with content from table2.

In table2 I have heights, widths and hashes. While the hashes correspond to the hashes in table1, there can be multiple rows with the same hash. When using this table, I take averages of the widths and heights:

mysql_query("SELECT AVG(width), AVG(height) FROM table2 WHERE hash='$hash'");

I need to associate these two tables together, where I grab the content from table1, as well as the avg width and height for that row from table2 (if there are any).

Beyond this, I don't know what to do:

mysql_query("SELECT * FROM table1, table2 ORDER BY table1.date DESC");

Any help is greatly appreciated. Thanks.

[syber]

you should be able to do this by using a derived table:

SELECT *
FROM table1 JOIN
(SELECT hash, AVG(width) AS avg_width, AVG(height) AS avg_height FROM table2) AS t2
ON table1.hash = t2.hash
ORDER BY table1.date DESC

[itledi]

Thank you very much!

The above code didn't exactly work for me, it returned an error about my mysql_fetch_array (I think because it didn't have any content?), so I slightly modified it to this:

mysql_query("SELECT * FROM table1 JOIN (SELECT hash, width, height FROM table2) AS t2 ON table1.hash=t2.hash ORDER BY table1.date DESC");

As is, it only returns data from table1 if there is a matching row in table2, which there isn't always. Is there a way to make a union of the two tables, and not an intersection of them?

My other problem is it won't run if I add AVG() around my width and height. Is this possible to make it run that way?

Thanks for any advice.

[syber]

If you want table1 to always return a row, make the JOIN a LEFT JOIN.

My mistake, to get AVG() to work make the following change:

(SELECT hash, AVG(width) AS avg_width, AVG(height) AS avg_height
FROM table2
GROUP BY hash) AS t2

[itledi]

That was exactly what I needed. Thank you very much for your help.