[thelist] Joining tables... in MySQL

Tab Alleman talleman at Lumpsum.com
Wed Apr 4 09:47:21 CDT 2007


> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Matt Warden
> Sent: Wednesday, April 04, 2007 10:35 AM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] Joining tables... in MySQL
> 
> 
> 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')
> 

I'd COALESCE to avoid the dreaded OR:


         WHERE
                 COALESCE(jobs.user,'4') = '4' AND
                 COALESCE(jobs.week,'2007-03-19') = '2007-03-19' AND
                 COALESCE(jobs.day,'2') = '2'



More information about the thelist mailing list