eth1 <[EMAIL PROTECTED]> wrote on 03/28/2006 03:04:13 PM:
>
> Hi All,
>
> I'm migrating to a contact relationship management system (CRM) for
one of
> my clients from a proprietary Access database. The CRM system can
import
> our donor's contact history, but only in a non-normalized format with up
to
> 10 donations per contact record in a single row of an Excel spreadsheet.
> Needless to say Access SQL doesn't seem up to the task of converting our
> Donations table (individual donation indexed by ID and ContactID) to
this
> funky format, so I've fired up MySQL in hopes of getting this done. But
I'm
> not sure how to proceed. I tried using a nutty left join on ContactID
but
> making sure the DonatioIDs for each joined row were different, but that
> didn't work (the SQL was:
>
> select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID
as
> cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b
ON
> a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
> a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND
b.DonationID
> != c.DonationID and a.DonationID != c.DonationID;
>
> It has numerous faults:
>
> * It's a pain to write (and I need to allow for up to 10 donations per
> user!)
> * It doesn't accound for different variations of the same selection
(e.g.
> notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3}
and
> {3,2,1} are all included in the result)
>
> I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
> someone out their can point me in the right direction. What I want is
> something like this:
>
> | ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
>
> Thanks in advance...off to Ruby to see if I can code some hack quick to
get
> this task done with...;)
>
> -Ethan
> --
> View this message in context: http://www.nabble.com/SQL-Question%3A-
> alternative-to-crazy-left-joins--t1357877.html#a3636912
> Sent from the MySQL - General forum at Nabble.com.
>
The "correct" way to model your information is to use the method you
describe as being used in the Access database. That data is normalized.
You even admit that you are denormalizing the data. The new CRM system is
imposing an artificial limit of 10 donations to any single contact. What
real-world rule says that after 10 donations, the contact is done? Or,
what real-world rule says to ignore the 11th or older contribution? These
artificial limit of only 10 donations in the donation history would be a
"deal breakers" for me.
Basically, the new design breaks several of the fundamental rules of
efficient database design. I would seriously doubt the capabilities of the
new system if this is how the backend is organized. I worry for your
client.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
