select by rand() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select by rand()

Author

Topic

Dhruv
Starting Member

2 Posts

Posted - 2009-04-12 : 22:10:42

I have connected my database from java, I am using mysql. I am able to retrieve images from database , but when I try and do it by random it gives me syntax error.

Here is the part of the java code

String cmd ="SELECT * FROM image ORDER BY rand() LIMIT 6000 " +
"JOIN tag_value tv ON tv.image_id = image.id " +
"WHERE (tv.tag_id = 1 AND "+ red_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 2 AND " + orange_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR "+
"(tv.tag_id = 3 AND " + yellow_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 4 AND " + green_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 5 AND " + baby_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 6 AND " + dark_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 7 AND " + pink_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 8 AND " + dark_red_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL)";

If I remove underlined part its working fine but when I add it its giving me an error.

Here is the error
1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN tag_value tv ON tv.image_id = image.id WHERE (tv.tag_id = 1 AND 0.0 BETWEEN' at line 1

What I am trying to do is that I have 8 sliders on front end part and between slider start val and endval specific images appear ,instead of that I want it to be selected randomly.

Thank you,
--
Dhruv Adhia
http://thirdimension.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-12 : 22:30:48

SQL Team is a SQL Server site, there's a MySQL forum here:

http://dbforums.com/

Dhruv
Starting Member

2 Posts

Posted - 2009-04-12 : 23:18:13

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 03:04:22

ORDER BY rand() LIMIT 6000 should be the last part of a query

String cmd ="SELECT * FROM image " +
"JOIN tag_value tv ON tv.image_id = image.id " +
"WHERE (tv.tag_id = 1 AND "+ red_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 2 AND " + orange_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR "+
"(tv.tag_id = 3 AND " + yellow_colorVal + " BETWEEN tv.STARTVAL and tv.ENDVAL) OR " +
"(tv.tag_id = 4 AND " + green_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 5 AND " + baby_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 6 AND " + dark_blue_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 7 AND " + pink_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL) OR" +
"(tv.tag_id = 8 AND " + dark_red_colorval + "BETWEEN tv.STARTVAL and tv.ENDVAL)
ORDER BY rand() LIMIT 6000 ";

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources