Abunch
02-25-2011, 05:09 PM
Trying to come up with a query that lets me pull a record for a single random public photo album picture. When everyone has only one photo album it's ok, but the minute you make more than one it breaks. I missed an association somewhere - like how to associate album and attachment...it was late.
Here's what I've got so far - can anyone help me find the missing links?
SELECT
attachment.attachmentid, albums.albumid, albums.userid
FROM
attachment,
(SELECT album.albumid, album.userid FROM album WHERE (state='public') ORDER BY rand() LIMIT 1) as albums
WHERE
(attachment.contenttypeid='8')
AND
(attachment.state='visible')
AND
(attachment.userid = albums.userid) ORDER BY RAND() LIMIT 1
Here's what I've got so far - can anyone help me find the missing links?
SELECT
attachment.attachmentid, albums.albumid, albums.userid
FROM
attachment,
(SELECT album.albumid, album.userid FROM album WHERE (state='public') ORDER BY rand() LIMIT 1) as albums
WHERE
(attachment.contenttypeid='8')
AND
(attachment.state='visible')
AND
(attachment.userid = albums.userid) ORDER BY RAND() LIMIT 1