I had almost given up doing this in a query, it works! I knew there had to be something like this, just couldn't quite snag it.... most of my queries are pedestrian to say the least.
It may be inefficient --and I see why it is-- but would it not be less so for the dbms to do this rather than using php to do essentially the same thing afterwards (which is what I did).
Roger Baklund wrote:
* 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]
