Left joining won't work because it will only grab one record from contacts and/or regarding if one exists, when there could be many. Thanks for the suggestion though.
Thanks
On Mar 15, 2004, at 4:47 PM, Rhino wrote:
Remarks interspersed below.
Rhino
----- Original Message ----- From: "Brent Baisley" <[EMAIL PROTECTED]> To: "MYSQL list" <[EMAIL PROTECTED]> Sent: Monday, March 15, 2004 4:32 PM Subject: Help with a tough query
I didn't think this was too hard when I first created it, but now that I'm loading test data it's not working as expected.
The core of the query is three tables: Event, Contact, and Regarding. There can be zero or more Contacts for each event and zero or more "Regardings" for each event. There are also 7 left joins I do to get other linked data, but that's working fine.
So, the question is, how do I get a list of events with possible multiple contacts and multiple regardings?
The problem I run into with my current query is that it fails when there are no contacts or regardings for an event.
What do you mean 'fails'? Do you get an error message - if so, what is it? -
or does it just not produce the result you want?
You're doing a natural/inner join here and that is why you never get EventsIs this possible in a single query? Here is a short version of my current query. SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events, Contacts, Regarding WHERE Events.EventID=Contacts.EventID AND Events.EventID=Regadings.EventID
when the Event is missing a Contact or a Regarding. Clearly, you need to do
a left/right type join to handle the cases where an Event has no
corresponding Contact or Regarding. Something like this:
SELECT DISTINCT EventID, Contact.Name, Regarding.Name FROM Events as e left join Contacts as c left join Regarding as r on Events.EventID = Contacts.EventID c AND Events.EventID=Regadings.EventID
I'm not saying that will work but it's closer than what you have so far. A
little tweaking should get this to work.
It is possible that I can make the Contacts database a one to many relation since 99% of the time there will be an associated contact record. I can make a "dummy" record for the 1% exception, but it feels "unclean". But if it makes it easier, so be it.
You really shouldn't have to make up dummy records to make this work. But
it's nice to know you're open-minded if all else fails ;-)
Right now I am running 4.0, but I'm not tied to any version yet.
Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
