Jump to content

Big query. Need help


owner

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/116933-big-query-need-help/
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/116933-big-query-need-help/#findComment-601854
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.