I am taking my first database class and need some info on what I need to change for my script to process. Currently I have 2 tables, an "orders" table and a "customers" table and this is how they are coded.
CREATE TABLE customers (
customer_id INT ,
customer_first_name VARCHAR(20),
customer_last_name VARCHAR(20) NOT NULL,
customer_address VARCHAR(50) NOT NULL,
customer_city VARCHAR(20) NOT NULL,
customer_state CHAR(2) NOT NULL,
customer_zip CHAR(5) NOT NULL,
customer_phone CHAR(10) NOT NULL,
customer_fax CHAR(10),
CONSTRAINT customers_pk
PRIMARY KEY (customer_id)
);
CREATE TABLE order (
order_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
shipped_date DATE,
employee_id INT,
CONSTRAINT orders_pk
PRIMARY KEY (order_id),
CONSTRAINT orders_fk_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
CONSTRAINT orders_fk_employees
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);
My script to join these two tables together is as follows:
SELECT or.order_id, or.order_date, or.customer_city,
cu.customer_first_name, cu.customer_last_name
FROM orders or INNER JOIN customers cu
ON or.customer_first_name = cu.customer_first_name
AND or.customer_last_name = cu.customer_last_name;
Now obviously, I know it is incorrect and may have multiple errors, so be gentle. I would love to know what I can do to make it work. Please advise.
Two things:
Don't use or as an alias. That's a reserved word.
Join by the foreign key.
The query should look like:
SELECT o.order_id, o.order_date, cu.customer_city,
cu.customer_first_name, cu.customer_last_name
FROM orders o
INNER JOIN customers cu
on o.customer_id = cu.customer_id