Richard Reina <[EMAIL PROTECTED]> wrote on 01/07/2005 03:31:26 PM:
> I am having trouble with a query that gives me
> activities that have not been "written up" but if
> these activities are a party they whould only appear
> in the query if they have been held, hence date will
> not='0000-00-00'
>
> If I write the query as so, no non-party activities
> will show up because the foreign key P_ID will be null
> and not match the p.ID.
>
> SELECT a.description
> FROM activity a, party p
> WHERE a.write_up IS NULL
> AND a.P_ID=p.ID
> AND p.date!="0000-00-00";
>
> IF I allow for a.P_ID null with logical OR (like so
> )it's a mess
>
> SELECT a.description
> FROM activity a, party p
> WHERE a.write_up IS NULL
> AND ((a.P_ID=p.ID AND p.date!="0000-00-00")
> OR a.P_ID IS NULL));
>
> Any help on how I can get this to work would be
> greatly appreciated.
>
> Richard
>
>
You need an OUTER JOIN not the implicit INNER JOIN you form by using the
comma to separate the table names
SELECT a.description
FROM activity a
LEFT JOIN party p
ON AND a.P_ID=p.ID
WHERE a.write_up IS NULL
AND (p.date!="0000-00-00"
OR a.P_ID IS NULL)
That will give you all records from activity that meets these conditions:
a) it wasn't a party
or b) it was a party and the party's date is not "0000-00-00"
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
