On Tuesday 04 July 2006 16:21, Nolan Rumble wrote: > SELECT COUNT(*) FROM temp GROUP BY recipient; > > and it will list all the email addresses and how much email they > sent/received. > > I suppose I can create another table which handles the variable length > recipients but I would like to avoid that as that would make the SQL > statements very complex and very hard to administer.
Trying to store multiple unique items in a single field is generally considered bad normalisation. Use linking tables, and store each e-mail address in a separate row. If table 1 contains an id for the mail and other unique data, table 2 contains one recipient per row (with unique IDs per recipient (use lowercase forcing on inserts so case differences don't matter)) and table 3 maps recipient IDs to mail IDs: SELECT count(*),recipient FROM table1 t1 LEFT JOIN table2 t2 ON t2.m_id=t1.m_id LEFT JOIN table3 t3 ON t3.r_id=t2.r_id GROUP BY recipient Not that complex, assuming I typed it right. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
