homepage Welcome to WebmasterWorld Guest from 54.198.148.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Supplement Content From Table2 With Table1
Would I use a JOIN command?
itledi




msg:3604744
 1:56 am on Mar 19, 2008 (gmt 0)

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




msg:3605183
 12:25 pm on Mar 19, 2008 (gmt 0)

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




msg:3605323
 2:58 pm on Mar 19, 2008 (gmt 0)

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




msg:3605423
 4:21 pm on Mar 19, 2008 (gmt 0)

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




msg:3606737
 9:10 pm on Mar 20, 2008 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved