Merchant table: 253 merchants - each merchant has one primary category
Category table: 161 categories with images (one image per category)
Merchants pick one primary category, some categories are more popular
than others, for instance the Dry Cleaners has the most merchants at 13.

I need to pick out 2 different merchants, not in the same category, and
put them in another table, so they will not get selected again for 30
days.

Right now, I'm doing something like the below but I'm getting dupes:


SELECT merchant, category, imgsrc
FROM merchants
JOIN category ON merchants.pricategory_id = category.id
ORDER BY RAND() <-- this is where the difficulty is
WHERE merchant NOT IN
(SELECT merchant
FROM usedmerchants
WHERE DATEDIFF(CURDATE(),added) < 30
)
LIMIT 2


If the entire table schemas are needed, I will be a happy to post them.
TIA.


--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

On 13-05-2011 06:30, Adrienne Boswell wrote:
Why do you think there's a difficulty here?

If i had to do this i would select one merchant at a time, and update
the 'added' directly to avoid this merchang from being chosen again
(within the next 30 days)

When picking the second merchant you can add something to the
WHERE-clause to avoid picking a second-merchant from the same category
as the first merchant. ( Which, as i read it, is not wat you want.)

--
Luuk

On 13-05-2011 21:03, Luuk wrote:
21:13:03 root@test[10]mysql> select min(i),max(i),count(i) from ints;
+--------+--------+----------+
1 row in set (0.00 sec)

21:13:18 root@test[11]mysql> select * from ints order by rand() limit 5 ;
+----+
5 rows in set (0.00 sec)

21:13:22 root@test[12]mysql> select * from ints order by rand() limit 5 ;
+----+
5 rows in set (0.00 sec)

The RAND() function seems to do a good job in getting random results

--
Luuk

On 13-05-2011 06:30, Adrienne Boswell wrote:

or:

SELECT merchant, category, imgsrc
FROM merchants
JOIN category ON merchants.pricategory_id = category.id
INNER JOIN usedmerchants ON merchants.pricategory_id = usedmerchants.id
AND DATEDIFF(CURDATE(),added) > 30
ORDER BY RAND()
LIMIT .....
You still need to select 1 at a time, because the second one should have
a different category...


--
Luuk