 | | | array type | array type 2006-07-04 - By Duncan Hill
Back
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/mysql?unsub=mysql@(protected)
|
|
|