Try this:
SELECT o.id, o.name, o.description
FROM #request.optionTable# o
LEFT OUTER JOIN #request.productOptionTable# po
ON po.optionid = o.id
WHERE o.id NOT EXISTS (SELECT * FROM #request.productOptionTable#
po WHERE po.optionid ='#url.productid#')
----- Original Message -----
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, September 25, 2002 10:55 PM
Subject: Re: JOIN Query (with query)
> Nope, that still pulls out the options that are associated with
the
> productid
>
> Paul Giesenhagen
> QuillDesign
>
> ----- Original Message -----
> From: "Matthew Walker" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, September 25, 2002 10:53 PM
> Subject: RE: JOIN Query (with query)
>
>
> > How about
> > WHERE (po.productid IS NULL) OR (po.productid NOT IN
> > ('#url.productid#'))
> >
> > > -----Original Message-----
> > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, 26 September 2002 3:43 p.m.
> > > To: CF-Talk
> > > Subject: Re: JOIN Query (with query)
> > >
> > >
> > > Odd the query didn't go through.. here it is again:
> > >
> > > SELECT o.id, o.name, o.description
> > > FROM #request.optionTable# o
> > > LEFT OUTER JOIN #request.productOptionTable# po
> > > ON po.optionid = o.id
> > > WHERE po.productid NOT IN ('#url.productid#')
> > >
> > >
> > > > I am trying to run a query that joins two tables and
> > > returns all the items
> > > that are listed in the optionTable, BUT NOT the ones who
have
> > > the productid
> > > of the url.productid in the productOption table.
> > > > (o.id = po.optionid)
> > > >
> > > > Here is the query I have thus far and it returns no
results .. if I
> > > replace NOT IN with = '#url.productid#' it returns all the
options
> > > associated with that product.
> > > >
> > > >
> > >
> >
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
