if it just can't be done with a query please somebody like Paul DuBois say so...
Colleen Dick wrote:
This stumped them in PHPbuilder...
I have a table lets call it moms each row in the moms table can have many kids, and some "kids" have >1 "mom". I have a kid table and I have a mxk map table to relate them.
what I want to do is select all the moms and for each mom I want a random one of its kids to go with it.
SELECT moms.m_id,m_name,kids.k_id,k_name FROM moms, mxk, kids WHERE moms.m_id = mxk.m_id AND mxk.k_id = kids.k_id GROUP BY moms.m_id
This works but gives me the same kid for each mom each time. if I say ORDER by RAND() at the end, it just puts the moms in random order. I want to randomly order the kids THEN do the group by and get the first kid in the random order. I've tried some HAVING things but that tells me mxk.m_id is an unknown column and it's definitely not.
How can I do this? I have a strong feeling there should be a way I can do it in a query and not a lot of postprocess in php. thanks
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]