CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Arepa Slayer
Arepa Slayer

Reputation: 443

SQL Server SELECT in JOIN

I'm working with two tables:

I need to generate a report which includes the item's code, number of times the item has been purchased, quantity available in stock and location.

So far, I've been trying this

SELECT CODE, SALES, QTY_AVAILABLE, LOCATION
FROM INVENTORY JOIN 
(
SELECT ITEMCODE, SUM(QTY_SOLD) AS SALES,
FROM INVOICE_HISTORY 
GROUP BY ITEMCODE
)
ON (CODE = ITEMCODE))

But it isn't working. Can anyone help me understand what I'm doing wrong?

Thank you.

Upvotes: 0

Views: 170

Answers (3)

user2989408
user2989408

Reputation: 3137

Try this

SELECT i.CODE, SUM(QTY_SOLD) SALES, i.QTY_AVAILABLE, i.LOCATION
FROM INVENTORY i
    JOIN INVOICE_HISTORY ih ON i.CODE = ih.ITEMCODE
GROUP BY i.CODE, i.QTY_AVAILABLE, i.LOCATION

Upvotes: 3

developering
developering

Reputation: 1325

You don't really need to write out that whole sub-select statement in the join as you've got there. What you'll want is to to use aliases for the table names and then you can just select the columns that you want, prefixed with the alias. It will be something like this:

SELECT INV.CODE, INV.SALES, INV.QTY_AVAILABLE, INV.LOCATION, HIS.ITEMCODE, HIS.SUM(QUANTITY) AS SALES 
FROM INVENTORY AS INV JOIN INVOICE_HISTORY AS HIS ON INV.CODE = HIS.ITEMCODE

The point is that you're giving the INVENTORY table an alias of INV and the INVOICE_HISTORY table an alias of HIS and then you reference both of them in the same select statement.

Hope that helps!

Upvotes: 1

Aftab Ahmed
Aftab Ahmed

Reputation: 1737

try this one

SELECT CODE, SALES, QTY_AVAILABLE, LOCATION
FROM INVENTORY Inv INNER JOIN INVENTORY INVOICE_HISTORY InvHis 
on Inv.CODE = InvHis.ITEMCODE

Upvotes: 1

Related Questions