CREATE TABLE Invoice ( Invoice_ID INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, I
ID: 3602759 • Letter: C
Question
CREATE TABLE Invoice(
Invoice_ID INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
I_Code VARCHAR(255) NOT NULL,
Customer_ID INT(11) NOT NULL,
Person_ID INT(11) NOT NULL,
I_Date VARCHAR(255),
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID) ,
FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
);
ALTER TABLE Invoice AUTO_INCREMENT=801;
INSERT INTO Invoice VALUES(NULL,"INV004",201,107,"2016-11-26");
INSERT INTO Invoice VALUES(NULL,"INV001",203,106,"2016-11-10");
INSERT INTO Invoice VALUES(NULL,"INV003",204,102,"2016-10-16");
INSERT INTO Invoice VALUES(NULL,"INV002",202,101,"2016-09-03");
SELECT * FROM Invoice;
CREATE TABLE Customer(
Customer_ID INT(11) PRIMARY KEY AUTO_INCREMENT,
C_Code VARCHAR(255),
C_Type VARCHAR(255),
Person_ID INT(11) NOT NULL,
C_Name VARCHAR(255),
Address_ID INT(11) NOT NULL,
FOREIGN KEY (Address_ID) REFERENCES Address(Address_ID),
FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
);
ALTER TABLE Customer AUTO_INCREMENT=201;
INSERT INTO Customer VALUES(NULL,"C001","G",102,"Tico's Restaurant",2);
INSERT INTO Customer VALUES(NULL,"C002","S",101,"The Sultan's Kite",1);
INSERT INTO Customer VALUES(NULL,"C003","S",103,"Cultiva Downtown",3);
INSERT INTO Customer VALUES(NULL,"C004","G",104,"Lazlo's Brewery & Grill",4);
INSERT INTO Customer VALUES(NULL,"C005","G",105,"LeadBelly",5);
INSERT INTO Customer VALUES(NULL,"C006","S",106,"Blue Sushi Sake Grill",6);
SELECT * FROM Customer;
How do I write a query to get all the invoices of a particular customer. (mySQL)
Explanation / Answer
If you want to get all invoices of a particular customer, say Customer 201.
Since the invoice table has foriegn key reference to Customer_ID, you can use it to get his invoices.
Query:
SELECT * from Invoice where Customer_ID = 201.
You can use the customer_id of your choice.