0

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.

1
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
1
  • I got it to work with this answer. Thank heaps :) – user6336941 Sep 8 '16 at 22:18
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.