Join to two tables

Question

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.

Answer 1

 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

Answer 2

You are going to want to select from a JOIN statement. In this case, an INNER JOIN.

Try something like this:

 SELECT i.itemName, i.pricePerUnit, c.itemQuantity 
 FROM Items AS i 
 INNER JOIN ContainsItems ON i.itemID = c.itemID 
 WHERE i.invoiceID = @yourInvoiceID

Having aliases for tables in joins helps prevent collisions.