Brent,
>The problem I run into with my current query is that it fails when
>there are no contacts or regardings for an event. 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
Did you try ...
SELECT Events.EventID, Contact.Name, Regarding.Name
FROM Events
LEFT JOIN Contacts USING (EventID)
LEFT JOIN Regarding USING (EventID)
GROUP BY Events.EventID;
?
PB
----- Original Message -----
From: Brent Baisley
To: MYSQL list
Sent: Monday, March 15, 2004 3: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. 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
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.
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]
