Big query. Need help

[owner]

Hello,

 

I am running this query:

 

SELECT gc.id, gc.name, gc.description, gc.date, g.id
FROM Gallery_cats gc
LEFT JOIN Gallery g ON g.cat_id = gc.id
ORDER BY gc.id DESC

 

and I need some help on it.  With that query, you will get multiple rows for g.id.  How can I get this so that it will randomly select 1 row out of whatever rows are in g.id?

 

Thank you very much in advance,

-owner

[cooldude832]

read

 

http://www.phpfreaks.com/forums/index.php/topic,125759.0.html

 

and then add a LIMIT 1 to it with the randomizing in the above link

[owner]

So it would look something like this?

 

SELECT
id, name, description, date
FROM
Gallery_cats AS gc
INNER JOIN 
(
    SELECT
    ROUND(RAND() * (SELECT MAX(id) FROM Gallery LIMIT 1)) AS id
) AS g 
ON
gc.id >= g.cat_id 
ORDER BY
gc.date DESC

 

Also, congratz on 4000 posts!

[owner]

Update:

I have this code working:

 

SELECT * FROM tablename
WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM tablename ) )
ORDER BY id ASC
LIMIT 1

 

however, that is selecting the wrong id, so I don't think we need this one.  In this code:

SELECT
id, name, description, date
FROM
Gallery_cats AS gc
INNER JOIN 
(
    SELECT
    ROUND(RAND() * (SELECT MAX(id) FROM Gallery LIMIT 1)) AS id
) AS g 
ON
gc.id >= g.cat_id 
ORDER BY
gc.date DESC

 

I get:

#1052 - Column 'id' in field list is ambiguous

 

So I try:

SELECT
gc.id, gc.name, gc.description, gc.date
FROM
Gallery_cats AS gc
INNER JOIN 
(
    SELECT
    ROUND(RAND() * (SELECT MAX(id) FROM Gallery LIMIT 1)) AS id
) AS g 
ON
gc.id >= g.cat_id 
ORDER BY
gc.date DESC

 

but get:

#1054 - Unknown column 'g.cat_id' in 'on clause'

 

but I don't know what else to do now after this, as this code doesn't know what cat_id and probably doesn't know what table it belongs to.

 

Any help would be greatly appreciated.

-Owner

[fenway]

How can it be selecting the "wrong id"? It's random...