Re: SQL Question: alternative to crazy left joins?
Thanks Shawn, Believe you me, I share your reaction to this architecture...I had to spend 2 hours coding a ruby script to get the data into the kludgy form needed for the data import (though I do find that thing kind of fun...but it's not the best use of my time on the job). Fortunately the data limit doesn't seem to be imposed by the apps underlying db schema, only by the importing process, as contacts can be associated with an unlimited number of donations in the program but only 10 donations can be imported at a time. Very few database apps I've used seem to be free from at least a few architectural or interface flaws such as this one, and so often it seems one needs to reach a compromise in order to settle on a reasonably priced piece of software with an intuitive, powerful interface and decent engineering. Still, though, I wonder if there's way to solve this problem using straight SQL. Ethan -- View this message in context: http://www.nabble.com/SQL-Question%3A-alternative-to-crazy-left-joins--t1357877.html#a3654455 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]
SQL Question: alternative to crazy left joins?
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]
Re: SQL Question: alternative to crazy left joins?
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
