Join to two tables

join;

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.

Best Answer

 SELECT *
 FROM ContainsItems ci
 INNER JOIN Items i    ON ci.itemID    = i.itemID 
 INNER JOIN Invoice iv ON ci.invoiceID = iv.invoiceID 
 WHERE ci.invoiceID = @yourInvoiceID
Related Question