CODEWITHSUNDEEP
mysqlsql
Jason Fel
Jason Fel

Reputation: 991

How come RAND() is messing up in SQL subquery?

My goal is to select a random business and then with that business' id get all of their advertisements. I am getting unexpected results from my query. The number of advertisement rows returned is always what I assume is the value of "SELECT id FROM Business ORDER BY RAND() LIMIT 1". I have 3 businesses and only 1 business that has advertisement rows (5 of them) yet it always displays between 1-3 of the 5 advertisements for the same business.

SELECT * FROM Advertisement WHERE business_id=(SELECT id FROM Business ORDER BY RAND() LIMIT 1) ORDER BY priority

Business TABLE: Business TABLE

Advertisement TABLE: Advertisement TABLE

Data for Advertisement and Business tables:

INSERT INTO `Advertisement` (`id`, `business_id`, `image_url`, `link_url`, `priority`) VALUES
(1, 1, 'http://i64.tinypic.com/2w4ehqw.png', 'https://www.dennys.com/food/burgers-sandwiches/spicy-sriracha-burger/', 1),
(2, 1, 'http://i65.tinypic.com/zuk1w1.png', 'https://www.dennys.com/food/burgers-sandwiches/prime-rib-philly-melt/', 2),
(3, 1, 'http://i64.tinypic.com/8yul3t.png', 'https://www.dennys.com/food/burgers-sandwiches/cali-club-sandwich/', 3),
(4, 1, 'http://i64.tinypic.com/o8fj9e.png', 'https://www.dennys.com/food/burgers-sandwiches/bacon-slamburger/', 4),
(5, 1, 'http://i68.tinypic.com/mwyuiv.png', 'https://www.dennys.com/food/burgers-sandwiches/the-superbird/', 5);

INSERT INTO `Business` (`id`, `name`) VALUES
(1, 'Test Dennys'),
(2, 'Test Business 2'),
(3, 'Test Business 3');

Upvotes: 1

Views: 958

Answers (3)

αNerd
αNerd

Reputation: 528

To retrieve rows in random order use SELECT * Instead Of Id and then query for its id.

SELECT * FROM Advertisement WHERE business_id=(SELECT ID FROM (SELECT * FROM Business ORDER BY RAND() LIMIT 1) as table1)

In this case with your example data, only when rand returns 1 you get results.

Upvotes: 0

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

You're assuming your query does something it doesn't do.

(SELECT id FROM Business ORDER BY RAND() LIMIT 1) isn't materialized at the beginning of the query. It's evaluated for each row... so for each row, we're testing whether that business_id matches the result of a newly-executed instance of the subquery. More thorough test data (more than one business included) should reveal this.

You need to materialize the result into a derived table, then join to it.

SELECT a.* 
  FROM Advertisement a
  JOIN (
        SELECT (SELECT id 
                  FROM Business 
                 ORDER BY RAND() 
                 LIMIT 1) AS business_id
        ) b ON b.business_id = a.business_id;

The ( SELECT ... ) x construct creates a temporary table that exists only for the duration of the query and uses the alias x. Such tables can be joined just like real tables.

MySQL calls this a Subquery in the FROM Clause.

Upvotes: 3

user1881277
user1881277

Reputation:

Try following query

SELECT * FROM Advertisement WHERE business_id = (select floor(1 + rand()* (select count(*) from Business)));

Upvotes: 0

Related Questions