[Resolved] random of group by

[marathoner]

I have a table lets call it moms
each row in the moms table can have many kids, so for that 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 kid to go with it.

Something like

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 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 but that's a syntax error.

How can I do this? thanks

[jayant]

SELECT moms.m_id,m_name,kids.k_id,k_name FROM
moms, mxk, kids GROUP BY moms.m_id HAVING moms.m_id = mxk.m_id
AND mxk.k_id = kids.k_id

you should have had done better database design:-

mom - table
id
.....

kid-table
kidid
momid <- id of the mom of the kid
.......

then things would hve had been easier.

[marathoner]

Hi, thanks for the suggestion.

Unfortunately, in the real world
I do need a map table because in the real
application a "kid" might have > 1 "mom"
I am just trying to make question minimalist 😉

I haven't done much with HAVING because it never seems to like what I put in it.

I even created bogus tables called moms kids and mxk to reduce to the minimal. I used your exact query and it says:
Unknown column 'mxk.m_id' in 'having clause'

The table mxk definitely has a column called m_id.

I am using 3.23 ? Now what?

[dnszero]

I think you might be better off using PHP for the
random portion. Select all the moms and all of their
kids and then use php to choose 1 random kid for
each mom.

You might be able to play around with the sql but,
afaik, you will always get the same result unless
using RAND() and I don't see any easy way around
getting the RAND to happen before the GROUP BY.

If you were using mysql 4.1, you could potentially
work around this with a subselect, but I'm not sure if
that would work well or even be much easier.

[dnszero]

Originally posted by jayant

you should have had done better database design:-

mom - table
id
.....

kid-table
kidid
momid <- id of the mom of the kid
.......


then things would hve had been easier.

How exactly would that make things easier? It seems like his current design is well normalized and efficient. This particular issue, though, I don't see as being any easier to resolve with your proposed design changes, but please, explain if I'm missing something.

[marathoner]

Thanks for your thoughts...

it would have been easier to make each kid simply point back to its mom like jayant said if the relation had been a simple 1:many.

These "moms" can have thousands of offspring. it's gonna be a pretty big dataset if I suck in all of 'em.

Of course I can use PHP to select a kid from the batch, but I'm always looking for some tricky 🆒 way to make mySQL do all the work.

[marathoner]

SELECT moms.m_id,m_name,  MAX(CONCAT(RAND(),'|',kids.k_id,'|',k_name)) AS data
  FROM moms
   NATURAL JOIN mxk
   NATURAL JOIN kids
   GROUP BY moms.m_id, m_name

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

All your PHP has to do is explode data.

Many thanks to Roger Baklund and
[e-mail]