a difficult query!!

[cram81]

hi im having trouble with a query and would appreciate any help.

i have tables:

user (username, pic_id, qNum)

images (pic_id, path)

I want to construct a query which returns the users pic_id and 8 other random pic_id 's from the images table not including the one already got from the user table.

i know about ORDER BY RAND() and limiting the result set ... but i cant seem to construct the query properly...

so far i am using ...

SELECT user.user_id,user.pic_id,images.pic_id
FROM user, images
WHERE images.pic_id != '2' // this is the pic in user table
AND user.user_id = '1' // this is the current user
ORDER BY RAND() LIMIT 8

this gives 8 pic_id's from the images table not including the one in the user table.

How can i append the one in the user table to this?

I have tried union but i couldnt fugure that out properly.... do i have to write the 8 to maybe a temp table then add the extra one, them do a select statement on that temp table or is there an easier way??
thanks

 

[r937]

fascinating problem

UNION would actually be best for this, but people never mention which version of mysql they're on, and any time i suggest UNION, they're invariable on an older version that doesn't suport it...

you said "the" user, so i will assume you want to run this query for only one user at a time

(it will not work for more than one)

select u.pic_id, i.pic_id
from user u
cross
join images i
where username='fred'
and u.pic_id <> i.pic_id
order by rand()
limit 8

the user's pic_id will be in the first column on each of the 8 rows



rudy

http://r937.com/

SQL Consulting

 

[cram81]

thanks for your reply...
i read up on unions before u i saw ur reply and came up with this:

(select pic_id FROM images WHERE pic_id != '2' ORDER BY RAND() LIMIT 8)
UNION SELECT pic_id FROM user WHERE user.user_id = '1' ORDER BY RAND();

it seems to work... and it puts everything into the one column which is handy for me...

thanks n e way...

 

[r937]

it &quot;seems&quot; to work, but

1) there will be 9 rows and you don't know which one is the user's pic

2) there's the possibility of a dupe (you did say &quot;not including the one already got from the user table&quot


you're welcome anyway

;-)

rudy

http://r937.com/

SQL Consulting

 

[tshot]

Hi r937

Is cross join any diff from ',' (comma) join ?

from user u cross join images i

and i think cram81 wnat totl 9 rows
How can i append the one in the user table to this?.

Howver, duplicate problem is definetly there. and so ur solution with LIMIT 9 is the ans or do in two step in a temp table (not so elegant but workable. I dont' know if RANDOM() uses Index. I think it should)




----------------
ur feedback is a very welcome desire

 

[cram81]

just out of curiousity does any one know if connector/j jdbc driver for java supports union?

ive been trying it in my code but keep getting errors??

 

[r937]

remember my comment above...

&quot;UNION would actually be best for this,
but people never mention which version of
mysql they're on, and any time i suggest UNION,
they're invariable on an older version that doesn't
support it...&quot;

i withdraw the first part, about UNION being best for this problem

cram81, i don't know about your driver, but mysql itself will not support UNION before version 4.0

tshot, you are right, a cross join is obtained using table list syntax by leaving out the join condition

you are also right that the duplicate problem will still be there

UNION does remove dupes, but then there is really no way to flag which of the 8 or 9 rows is the user's pic

rudy

http://r937.com/

SQL Consulting