CODEWITHSUNDEEP
mysqlsql
Steberz
Steberz

Reputation: 5

Referencing Two Tables in SQL?

So the question is asking this:

List the order number and order date for each order that was placed by Johnson's Department Store, but does not contain and order line for a Gas Range.

Below is what I already tried and the error it keeps giving me. So I know I am supposed to select the ORDER_NUM and ORDER_DATE because that it what it's asking to list. I put from ORDERS because that's where ORDER_NUM and ORDER_DATE are, and I put part because that was the only place where I could find Gas Range as data being inserted. 608 is the customer number for Johnson's Department Store, and the last line is what I cannot figure out. How do I say the description does not contain Gas Range?

SELECT ORDER_NUM, ORDER_DATE
    -> FROM ORDERS, PART
    -> WHERE CUSTOMER_NUM='608'
    -> AND PART.DESCRIPTION NOT IN 'Gas Range';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Gas Range'' at line 4

Upvotes: 0

Views: 285

Answers (3)

M. Rezaeyan
M. Rezaeyan

Reputation: 408

Use NOT LIKE instead of NOT IN

SELECT ORDER_NUM, ORDER_DATE
    -> FROM ORDERS, PART
    -> WHERE orders.id = part.order_id and CUSTOMER_NUM='608'
    -> AND PART.DESCRIPTION NOT LIKE '%Gas Range%';

The IN operator allows you to specify multiple values in a WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

The "%" sign is used to define wildcards (missing letters) both before and after the pattern.

Upvotes: 1

PlsqlDev
PlsqlDev

Reputation: 236

You can use the operator <>. I also noticed that there is no join condition between the two tables.

SELECT ORDER_NUM, ORDER_DATE
    -> FROM ORDERS, PART
    -> WHERE CUSTOMER_NUM='608'
    -> AND PART.DESCRIPTION <> 'Gas Range';

Upvotes: 0

dognose
dognose

Reputation: 20889

NOT IN does not work as you expect it here. (it's used along with a set of values)

You need PART.DESCRIPTION NOT LIKE '%Gas Range%'

Upvotes: 3

Related Questions