Not looking for answers. But this is driving me nuts.

#4 Write SQL SELECT statement that obtains the sum of QUANTITY_SOLD on
1/16/2011 from the INVOICE table for the publisher whose publisher_id is 2.
Hint: You will need to use an AND condition in the WHERE clause and also join the
book table in this query to obtain the publisher_id (25%)
Results:
sum(invoice_line_item.quantity_sold)
------------------------------------
92
1 Row(s) affected

Here is what I have so far.

SELECT SUM(ILI.QUANTITY_SOLD)
FROM INVOICE I
JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
JOIN BOOK B ON B.PUBLISHER_ID=B.PUBLISHER_ID
JOIN PUBLISHER P ON I.INVOICE_ID=P.PUBLISHER_ID
WHERE I.INVOICE_DATE='2011-1-16'

My problem is when i run the sum, i get something like 140 instead of 92.
When I take out my sum and run the query, i found that its counting some things more than once. its counting all of the books sold and then counting again of all the books by that publisher.

I think I am lacking the understanding of Joins properly.

What is the syntax of a join? maybe that would help me.

looking at your requirement
Write SQL SELECT statement that obtains the sum of QUANTITY_SOLD on
1/16/2011 from the INVOICE table for the publisher whose publisher_id is 2.

don't see that bit in your SQL

your problem is right here --

JOIN BOOK B ON B.PUBLISHER_ID=B.PUBLISHER_ID

that's going to join every single row in the book table (except any books which don't have a publisher_id)

I fixed the publisher id =2 after i posted. sorry.



SELECT SUM(ILI.QUANTITY_SOLD)
FROM INVOICE I
JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
JOIN BOOK B ON B.ISBN=B.ISBN
JOIN PUBLISHER P ON P.PUBLISHER_ID=P.PUBLISHER_ID
WHERE P.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'

So I am working on invoice.

Invoice has Invoice_ID and invoice_date.
Inovice Line Item has invoice ID, ISBN and Quantity sold.
Book has ISBN, TITLE, Subject ID, publisher ID, year published and price.
Publisher has publisher ID and Publisher name.

So I revamped it and this is where I am now.

SELECT SUM(ILI.QUANTITY_SOLD)
FROM INVOICE I
JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
JOIN BOOK B ON B.ISBN=B.ISBN
WHERE B.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'

Since I am working on invoice, and the quantity sold on invoice line item, I joined invoice line item in line 3. Also, since I need the publisher, I got that from book since book shares the isbn with invoice line item and also contains the publisher id. But do i need to link publisher since publisher ID is a foreign key to book?

this --

JOIN BOOK B ON B.ISBN=B.ISBN

will end up joining every single row in the book table (except any books which don't have an isbn)

here's a tip: less haste, more speed

look carefully at at the sql you have written, before you test it

Bingo. I was linking the book wrong. Thanks! only one more assignment to do by midnight.

SELECT SUM(ILI.QUANTITY_SOLD)
FROM INVOICE I
JOIN INVOICE_LINE_ITEM ILI ON ILI.INVOICE_ID=I.INVOICE_ID
JOIN BOOK B ON ILI.ISBN=B.ISBN
WHERE B.PUBLISHER_ID=2 AND INVOICE_DATE='2011-1-16'