I have created three table using with the following code:
CREATE TABLE Invoices (
invoiceID nvarchar(5) NOT NULL PRIMARY KEY,
invoiceDate date,
);
CREATE TABLE ContainsItems (
invoiceID nvarchar(5) REFERENCES Invoices(invoiceID),
itemID nvarchar(5) REFERENCES Items(itemID),
itemQuantity numeric(2, 0)
);
CREATE TABLE Items (
itemID nvarchar(5) NOT NULL PRIMARY KEY,
itemName nvarchar(20),
unit nvarchar(20),
pricePerUnit float
);
For a given Invoice (invoiceID), how am I am to query a list all it's Items (itemNames) and their corresponding price per unit, and the number of units (itemQuantity) invoiced?
I've been trying this all night and got stumped.