Workaround for subquery

[NetworkHound]

I am porting a project to MySql 4.0 and can't use the following query which is designed to return a list of options that have not been selected by a particular customer.

SELECT OptionID from OptionLookUp
WHERE NOT EXISTS
(SELECT *
FROM CustomerOptionsTable
WHERE CustomerOptionsTable.OptionID=OptionLookUp.OptionID
AND CustomerOptionsTable.CustomerID=7689)

Can anyone suggest a workaround that will return either :

1) the same result

or

2) a recordset that includes all available options and distinguishes between selected and unselected options for the particular customer.

All help gratefully received.

 

[sleipnir214]

Have you taken a look at this page from the MySQL online manual?

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[NetworkHound]

Thanks for the response.

I have checked out the manual but the code snippet

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;


doesn't really deliver the results I need. In the CustomerOptionsTable there are a few rows relevant to a particular customer showing the options he has selected. I need to identify the unselected options for that customer only.

I can't work out how to modify the code in the manual to make it specific to one customer. As given it just returns nothing as every option has been selected by somebody.

 

[sleipnir214]

Add more elements to you WHERE clause.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!