[thelist] Joining tables... in MySQL

r937 rudy at r937.com
Wed Apr 4 11:07:34 CDT 2007

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:
>      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
  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?


More information about the thelist mailing list