Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

SQL HAVE statement using text

Given the following tables & Columns:

 

Customer

customer_id (PK)

customer_name

 

Order

Order_id (PK)

Customer_id (FK)

Order_Date

 

Order_Line

Order_id (PK) (FK)

Line_no (PK)

Item_id (FK)

Qty

Extended_price

 

Item

Item_id

Item_name

Item_description

Price

 I am trying to write the SQL statement that retrieves the list of customers (name) who did not have any orders for product name "X" on a given date? I am having some problems

0
cozza13
Asked:
cozza13
  • 5
  • 4
1 Solution
 
AdrianSRUCommented:
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
0
 
cozza13Author Commented:
Hi that bought up an error when trying to left join the Order_line table to the Order table
0
 
AdrianSRUCommented:
What was the error message?


--Adrian
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
cozza13Author Commented:
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)
0
 
AdrianSRUCommented:
What version of MySQL are you using?


--Adrian
0
 
cozza13Author Commented:
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.
0
 
cozza13Author Commented:
I have a feeling that it can't be done with SQL on its own.
0
 
AdrianSRUCommented:
Try it without some of the parenthesis:

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;

>I have a feeling that it can't be done with SQL on its own.
It can, but the problem is that you aren't putting it directly into MySQL.  The intermediate stuff is causing the problem.


--Adrian
0
 
cozza13Author Commented:
ok thanks for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now