need to create a query that joins these 3 tables, without the duplicate column which would be the shop_id. i cant get them to join without that column duplicating. help would be appreciated thanks! CREATE TABLE COFFEE_SHOP ( shop_id int, shop_name VARCHAR(50), city VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) ); CREATE TABLE EMPLOYEE ( employee_id int, first_name VARCHAR(30), last_name VARCHAR(30), hire_date Date, job_title VARCHAR(30), shop_id int, PRIMARY KEY (employee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id) ); CREATE TABLE COFFEE ( coffee_id int, shop_id int, supplier_id int, coffee_name VARCHAR(30), price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id), FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id) );
i need to create a query that joins these 3 tables, without the duplicate column which would be the shop_id. i cant get them to join without that column duplicating. help would be appreciated thanks!
CREATE TABLE COFFEE_SHOP (
shop_id int,
shop_name VARCHAR(50),
city VARCHAR(50),
state CHAR(2),
PRIMARY KEY (shop_id)
);
CREATE TABLE EMPLOYEE (
employee_id int,
first_name VARCHAR(30),
last_name VARCHAR(30),
hire_date Date,
job_title VARCHAR(30),
shop_id int,
PRIMARY KEY (employee_id),
FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id)
);
CREATE TABLE COFFEE (
coffee_id int,
shop_id int,
supplier_id int,
coffee_name VARCHAR(30),
price_per_pound NUMERIC(5,2),
PRIMARY KEY (coffee_id),
FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id),
FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id)
);
Step by step
Solved in 2 steps
