Max_Davis
asked on
How come this random query doesn't work?
<CFQUERY name="q_GetImages" datasource="Rating">
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1 AND NOT EXISTS (SELECT 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([ImageID])
</CFQUERY>
For some reason this isn't returning random records, why not?
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1 AND NOT EXISTS (SELECT 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([ImageID])
</CFQUERY>
For some reason this isn't returning random records, why not?
Ooops! Sorry, I forgot something.
It should be this way :
WHERE II.Approved = 1 AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
Regards!
eNTRANCE2002 :-)
It should be this way :
WHERE II.Approved = 1 AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
Regards!
eNTRANCE2002 :-)
Try this query:
<CFQUERY name="q_GetImages" datasource="Rating">
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1
AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([RT.ImageID])
</CFQUERY>
Perhaps this will return any records.
<CFQUERY name="q_GetImages" datasource="Rating">
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1
AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([RT.ImageID])
</CFQUERY>
Perhaps this will return any records.
<CFQUERY name="q_GetImages" datasource="Rating">
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1 AND NOT EXISTS (SELECT * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([II.ImageID])
</CFQUERY>
SELECT TOP 1 *
FROM ImageIndex II
WHERE II.Approved = 1 AND NOT EXISTS (SELECT * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
ORDER BY rnd([II.ImageID])
</CFQUERY>
it's not returning random results because even though the sql spec calls for it to, two things might be happening:
1) your dbms probably just returns the first record in the db file
2) if the dbms is set up to actually find a random record instead of returning the first one, you have an index set up on one or more fields so it is returning based on the index.
1) your dbms probably just returns the first record in the db file
2) if the dbms is set up to actually find a random record instead of returning the first one, you have an index set up on one or more fields so it is returning based on the index.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
WHERE II.Approved = 1 AND NOT EXISTS (SELECT 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
If not change it with this one, if this is what you are referring :
WHERE II.Approved = 1 AND NOT EXISTS (SELECT TOP 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
Goodluck!
eNTRANCE2002 :-)