[thelist] Joining tables... in MySQL

[Matt Warden]

On 4/4/07, Tris <beertastic at gmail.com> wrote:
> it's my WHERE clause that's causing the grief..
>
> SELECT jobType.name, jobs.user, jobs.jobNo, jobs.jobType, jobs.hours, jobs.day
>
>         FROM jobs
>
>         LEFT JOIN jobType on
>         jobs.jobType = jobType.id
>
>         WHERE
>                 jobs.user = '4' AND
>                 jobs.week = '2007-03-19' AND
>                 jobs.day = '2'
>
>
> Take it out and I get plenty of NULL results...
> Hmmmm

Well that's obvious, no? If there is no jobs record for a jobType, all
the jobs fields will be NULL. If they are NULL, then they won't equal
'4', '2007-03-19', nor '2', right?

Perhaps:

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


-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.