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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
