Well .. that did the trick .. I had to tweak alittle, but that was the idea .. very backwards but works!!
Thank You! Paul Giesenhagen QuillDesign ----- Original Message ----- From: "S. Isaac Dealey" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 9:36 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've not worked with mySQL, so I don't know if you can use WHERE NOT EXISTS > .. I think that's sql server specific... Otherwise, you might try > > LEFT JOIN product_options po on > ( po.optionid = options.optionid > and po.productid = #url.productid# ) > WHERE po.optionid IS NULL > > It seems counter-intuitive, I know -- but at least in SQL Server ( not sure > about mySQL, etc. ) what this does is perform the join product_options table > and then remove any records from the result set where the left join > succeeded. > > S. Isaac Dealey > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
