What about this? SELECT DISTINCT options.id, options.name, options.description, product_options.productid FROM options LEFT JOIN product_options ON options.id = product_options.optionid WHERE product_options.productid <> 3
----- Original Message ----- From: "Paul Giesenhagen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 8:58 AM Subject: Re: JOIN Query (revised) > Yes, I have tried not in ... and no, it is not a list... > > Ok here is the table setup: (trimed with only useful info) > > OPTIONS: > id, name, description > > PRODUCT OPTIONS > optionid, productid > > I want a list of all the options that are not associated with the > #url.productid# (found in the product options table, productid column). > > So if the option table has id's 1,2,3,4 > > and the product options table have > optionid, productid > 2 3 > 4 3 > > And url.productid = 3, I only want option id 1 and 4 > > I cannot use a sub query (already have that one working fine, this is for > mySQL). > > I hope this makes sense! > > Thanks > Paul Giesenhagen > QuillDesign > > > ----- Original Message ----- > From: "Greg Luce" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, September 26, 2002 8:31 AM > Subject: RE: JOIN Query > > > > Why "NOT IN"? Is the url.productid a list? Have you tried "<>"? > > > > -----Original Message----- > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, September 25, 2002 11:40 PM > > To: CF-Talk > > Subject: OT: JOIN Query > > > > 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. > > > > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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
