Page 1 of 1

Query for pulling a banner based on a flag

Posted: Sun Mar 07, 2010 11:24 am
by s.dot
I'm trying to write a query where a member can rotate banner advertisements only if a flag called allow_banners is = 1 in the users table.

To select the banner my query is simple:

Code: Select all

SELECT * FROM `banners` ORDER BY RAND() LIMIT 1
But to add in the user flag to the query it gets complicated (seems like it should be easy, maybe I've been working too much).. something like

Code: Select all

SELECT * FROM `banners` WHERE `user_id` IN(SELECT `id` FROM `users` WHERE `allow_banners` = 1) ORDER BY RAND() LIMIT 1
This feels really ugly. It might work but I haven't tried it due to how ugly it feels. Is there a more elegant solution or is that right?

Re: Query for pulling a banner based on a flag

Posted: Sun Mar 07, 2010 2:55 pm
by mikosiko
... one of this probably will be my choice

Code: Select all

 
SELECT a.* FROM banners a, users b
 WHERE a.user_id = b.id
     AND b.allow_banners = 1
 ORDER BY RAND() LIMIT 1
 

Code: Select all

 
SELECT a.* FROM banners a JOIN  users b ON ( a.user_id = b.id)
 WHERE  b.allow_banners = 1
 ORDER BY RAND() LIMIT 1