* Colleen Dick
> 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
Try this:
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
The MAX(CONCAT-thingy is known as the MAX-CONCAT trick, it is described
here:
<URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html >
This is relatively heavy on the server, but this may not be significant if
the amount of data is small, i.e. a few thousand moms & kids should not be a
problem.
The natural join uses the column names to join, it seems this can be used in
your case, because the columns have the same name in the joined table. This
eliminates the need of a where clause in this case. The MAX-CONCAT trick
will also work with 'normal' joins, the way you had in your example.
You would need to split the data column in your application, in PHP it could
be something like this:
list($dummy, $k_id, $k_name) = explode("|",$myrow["data"]);
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
