[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