SQL HAVE statement using text

Try this:

SELECT Customer.customer_name FROM ((Customer LEFT JOIN Order ON (Customer.customer_id=Order.customer_id AND Order.order_date='yyyy-mm-dd')) LEFT JOIN Order_Line ON Order.order_id=Order_Line.order_id) LEFT JOIN Item ON (Order_Line.item_id=Item.item_id AND Item.item_name='product name') WHERE Item.item_id IS NULL ORDER BY Customer.customer_name;

By using a LEFT JOIN, all of the customers will be included in the result set before the WHERE condition is considered.  If the customer has an order for the product on the date specified, then Item.item_id will have a value and "WHERE Item.item_id IS NULL" will remove that customer from the final results.


--Adrian

Hi that bought up an error when trying to left join the Order_line table to the Order table

What was the error message?


--Adrian

Unexpected token order requires SELECT in Statement [Select.....

it does this when I do the LEFT JOIN Order_Line ON Order.order_id=Order_Line.order_id)

What version of MySQL are you using?


--Adrian

well I was trying to do it in SQL on openoffice database. The I tried it in Cryatl Reports 11 and I get the error message. "join expression not supported" using the ODBC text driver.

I have a feeling that it can't be done with SQL on its own.

ok thanks for your help