SubQueries Selecting Random Pictures for Posts that contain Multiple Pictuers.

[chillexistence]

I have two Tables:
Table 1: POSTS
post_id, title, description, post_time

Table 2: PICTURES
picture_id, post_id, filename, viewed


[!--sizeo:3--][span style=\"font-size:12pt;line-height:100%\"][!--/sizeo--]Ok Basicaly i'm assigng multiple pictures to each post, but I want one SQL query statement to grab the latest 15 posts, but with each post I want it to only grab one picture (random) for each post, also want it to grab the sub of the hits for each picture under that post [!--sizec--][/span][!--/sizec--]

[b]POSTS TABLE[/b]
-------------------------------+------------+
| post_id | title | description | post_time |
-------------------------------+------------+
| 1 | Th isis a new title | must be a description of some sorts | 1137906000 |
| 2 | 2nd post | yup 2nd post | 1138770000 |
| 3 | Big long Title | if i can use up 95 chars | 1137992400 |


[b]PICTURES TABLE[/b]
+---------+------------+--------------------------------------------+
| post_id | picture_id | filename | viewed
+---------+------------+--------------------------------------------+
| 1 | 1 | 71631afterthestorm___southeastmichigan.jpg | 10 |
| 1 | 2 | 70438bridgesnow.jpg | 5 |
| 1 | 3 | 13813finallywhite.jpg | 3 |
| 1 | 4 | 46335thebeautyofsnow3.jpg | 1 |
| 1 | 5 | 2234itsbeginningtolooklikechristmas.jpg | 100 |
| 1 | 6 | 87418eveningglow2.jpg | 51 |
| 2 | 7 | 8765afterthestorm___southeastmichigan.jpg | 32 |
| 2 | 8 | 5521antisolarrays.jpg | 16 |
| 2 | 9 | 75361bluemosqueandhagiasofia.jpg | 5 |
| 2 | 10 | 42551bridgesnow.jpg | 6 |
| 2 | 11 | 27259darkshot.jpg | 8 |
| 2 | 12 | 2579doodlesbyjackfrost.jpg | 0 |
| 2 | 13 | 10944endofawinterday.jpg | 0 |
| 2 | 14 | 52402eveningglow2.jpg | 3 |
| 2 | 16 | 5692eveningsilk.jpg | 6 |
| 2 | 17 | 6489finallywhite.jpg | 10 |
| 2 | 18 | 97995fireinthesky.jpg | 15 |
| 2 | 20 | 61735fivemoreminutesanotherangle.jpg | 20 |
| 3 | 22 | 26410peppered.jpg | 25 |
| 3 | 23 | 69480onfrozenpond.jpg | 30 |
| 3 | 24 | 74988mountcheam.jpg | 6 |
| 3 | 25 | 67594momentsago.jpg | 3 |
| 3 | 26 | 10041lotsofsnow.jpg | 15 |
| 3 | 27 | 16440lightmeetsnight.jpg | 45 |
| 3 | 28 | 99927lateafternoon.jpg | 32 |
| 3 | 29 | 67191itsbeginningtolooklikechristmas.jpg | 12 |
| 3 | 30 | 22382iridescentclouds.jpg | 95 |
| 3 | 31 | 92893icysunrise.jpg | 1400 |
| 3 | 32 | 39515iceonthetrees.jpg | 6 |
| 3 | 33 | 63468huddletime.jpg | 5 |
| 3 | 34 | 72809heavysnowstorm.jpg | 4 |
| 3 | 35 | 20713heavyladened.jpg | 3 |
| 3 | 36 | 78126frigidsunset.jpg | 2 |
| 3 | 37 | 53299frigidmorning.jpg | 1 |
| 3 | 38 | 20698fivemoreminutesanotherangle.jpg | 0 |
| 3 | 39 | 71775firefoxtrashit.jpg | 5 |
| 3 | 40 | 50135finallywhite.jpg | 3 |
| 3 | 41 | 83561fireinthesky.jpg | 2 |
| 3 | 42 | 75422eveningsilk.jpg | 5 |
| 3 | 43 | 67954eveningglow.jpg | 6 |
+---------+------------+--------------------------------------------+



[!--sizeo:4--][span style=\"font-size:14pt;line-height:100%\"][!--/sizeo--][b]so an example of what i want the output to look like would be[/b][!--sizec--][/span][!--/sizec--]

+---------+------------+--------------------------------------------+
| post_id | picture_id | filename | viewed | title | desc
+---------+------------+--------------------------------------------+
| 1 | 3 | 13813finallywhite.jpg | 170 |
| 2 | 13 | 10944endofawinterday.jpg | 121 |
| 3 | 38 | 20698fivemoreminutesanotherangle.jpg | 1705 |


If there's a better way to store the necessary data in database I'd put all for storeing it differently. Any help would be greatly appreciated.

[fenway]

That's a little confusing. You can get the last 15 with a "ORDER BY post_time LIMIT 15", pull a random pictures with a subselect and an "ORDER BY RAND() LIMIT 1", but I don't know what you mean by "sub of hits" -- and the output you show has a "viewed" value that doesn't correspond to anything I can see in the tables. Please clarify.

[chillexistence]

The solution is as follows:

$sql = "SELECT posts.post_id,
title,
description,
post_time,
(SELECT filename
FROM pictures
WHERE pictures.post_id = posts.post_id
ORDER BY RAND()
LIMIT 1) AS filename,
(SELECT sum(viewed)
FROM pictures
WHERE pictures.post_id = posts.post_id ) as viewed
FROM posts
WHERE post_time <= ". time() ."
ORDER BY posts.post_time DESC
LIMIT 15";

[fenway]

Oh, sum, not "sub" -- that makes much more sense... glad you got it working (and thanks for posting the working query).