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
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');
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;
( 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