OOPS! the ON AND was a typo. Sorry! Should have been just ON. I am puzzled why it worked but I am glad it did.
That's what I get for too much cut-and-paste at the end of the day. I apologize to everyone! What actually made it work was not the ON AND (yuck!) but the LEFT JOIN. What you were trying to do was to search all records of the activity table that had no description except those that matched parties with no dates. The LEFT join allowed the engine to return all rows of the Activity table and fill in the data from the party table whenever the two matched up. Where the party table and the activity table didn't match up, the engine made it seem that EVERY COLUMN of the party table was NULL. Clearly this should not be true for any real row in the party table as you should have a Primary Key (which is by definition NOT NULL) on every table. By checking for a null PK value from the party table, we identify all activities that aren't parties. By comparing the date on the party to "0000-00-00" we were able to eliminate those activities that were parties but haven't been held yet. The parentheses in the WHERE clause are necessary to isolate the OR from the AND so that we met all of the conditions of your search. Again, I apologize for my typo. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Richard Reina <[EMAIL PROTECTED]> wrote on 01/09/2005 01:12:23 PM: > It worked! Thank you so very much for your very > elegant solution to this problem. Your expetese of > SQL is evident. I had never seen "ON AND", at first I > thought it was a typo, however I quickly learned that > it was part of the solution I was looking for. > > Once again, Thank you for your briliant help. > > Sincerely, > > Richard > --- [EMAIL PROTECTED] wrote: > > > 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 > > > > >
