> 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
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
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

Reply via email to