mysql query problem

Discussion in 'Databases' started by Jeremy Benson, Feb 7, 2016.

0
  1. #1
    Hello,

    I'm getting a syntax error in this query line.

    SELECT `title`, `link`, `image` FROM `advertisements` WHERE `live` = \'yes\' RAND()<(SELECT ((1/COUNT(*))*10) FROM `advertisements`) ORDER BY RAND() LIMIT 4;
    Code (markup):
    The syntax error is 'You have a syntax error new RAND.'

    Not sure if there should be an AND in there, or something else. It's selected 4 random rows.

    Thanks,
    Jeremy.
     
    Jeremy Benson, Feb 7, 2016 IP
  2. zonar

    zonar Member

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    31
    #2
    You have 2 conditions in the WHERE clause, but are missing the operator between them
     
    zonar, Feb 9, 2016 IP
  3. jyotisharma

    jyotisharma Greenhorn

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    21
    #3
    There is one mistake in this line, to resolve this issue firstly use this sintex
    SELECT `title`, `link`, `image` FROM `advertisements` WHERE `live` = '\'yes\' RAND()<(SELECT ((1/COUNT(*))*10) FROM `advertisements`) ORDER BY RAND() LIMIT 4;
     
    jyotisharma, Feb 16, 2016 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #4
    I don't get what you are trying to do with the rand comparison but I was able to get this running against a table in my database

    SELECT `id`, `title`, RAND() AS randresult
FROM `contents`
WHERE `contents`.`active` = 1
HAVING randresult < (SELECT ((1/COUNT(*))*10) FROM `contents`) 
ORDER BY `randresult`
LIMIT 4
    Code (markup):
    You need to only call rand() once otherwise you'll get completely different results and the order by clause will look like it's broken
     
    sarahk, Feb 17, 2016 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,807
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #5
    Have been thinking about this

    Is the subquery to limit the number of rows returned?
    If so, then you can just leave it out and let the limit 4 do the job
     
    sarahk, Feb 17, 2016 IP