Ed Since schrieb:
> Hello, I'm wondering if this is the most effective way of doing an outer
> join with 'extra criteria' (I don't feel like it's the best way):
>
> SELECT e.EventID, ue.Contact, ut.Discount
> FROM Event e
> LEFT OUTER JOIN
> (SELECT EventID, Contact FROM UserEvent WHERE UserId = 10) ue using
> (EventID)
> LEFT OUTER JOIN
> (SELECT EventID, Discount FROM UserEventType WHERE UserID = 10) ut
> using (EventTypeID)
> WHERE e.FromDate >= '2007-05-15'
>
> Essentially, I'm trying to refactor some legacy php which looks like:
>
> $q1 = mysql_query("SELECT EventID, EventTypeID FROM Event WHERE FromDate
>>= '2007-05-15'");
> while ($r1 = mysql_fetch($q1)) {
> $q2 = mysql_query("SELECT Contact FROM UserEvent WHERE UserId = 10 AND
> EventID = {$r1['EventID']}");
> $q2 = mysql_query("SELECT Discount FROM UserEventType WHERE UserId = 10
> AND EventID = {$r1['EventTypeID']}");
> // ...
> }
how about:
SELECT
e.EventID, ue.Contact, ut.Discount
FROM
Event e
LEFT JOIN
UserEvent ue
ON ue.EventID = e.EventID
AND ue.UserId = 10
LEFT JOIN
UserEventType ut
ON ut.EventTypeID = e.EventTypeID
AND ut.UserID = 10
WHERE
e.FromDate >= '2007-05-15'
optionally
HAVING
NOT IS_NULL(ue.Contact)
--
Sebastian Mendel
www.sebastianmendel.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
