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

2 Answers 2

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 :) Sep 8, 2016 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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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