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? > Is 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 > You're doing a natural/inner join here and that is why you never get Events 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
