[thelist] Joining tables... in MySQL

[r937]

mark, that "wellho.net" page you posted the link to is one of the most
poorly formatted and difficult to read pages that it has been my misfortune
to see in many weeks

tris's problems have been solved elsewhere (yes, by me)


to matt, my dear friend, who said --

> Perhaps:
>
> WHERE
>      jobs.id IS NULL
>     OR ( jobs.user = '4' AND  jobs.week = '2007-03-19' AND jobs.day = '2')

perhaps not   ;o)

the sane approach here is to place the conditions involving columns of the
right table of a LEFT OUTER JOIN into the ON clause


SELECT jobType.name
     , jobs.user
     , jobs.jobNo
     , jobs.jobType
     , jobs.hours
     , jobs.day
  FROM jobType
LEFT OUTER
  JOIN jobs
    ON jobs.jobType = jobType.id
   AND (
          (
          jobs.user = 4
      AND jobs.week = '2007-03-19'
          )
       OR (
          jobs.day in ( 0, 3 )
          )
       )


of course, i'm not sure what tris was trying to do (he never said), so those
conditions may not be the right ones

if anyone is interested in the difference between conditions in the WHERE
clause versus conditions in the ON clause, please see this article --

  LEFT OUTER JOIN with ON condition or WHERE condition?
  <http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_gci1125854_tax301455,00.html>


rudy
http://r937.com/