[porcupine75]

Somebody, please help me make an SQL for this:

TABLE_itemList

ITEM_CODE  DESCRIPTION
------------------------------
001	   Pencils
002	   Erasers
003	   Scissors
------------------------------

TABLE_itemLedger

ITEM_CODE  DATE 	STOCKIN	STOCKOUT  POSTED
------------------------------------------------------------------------
001	10/08/08	   5 				  False
002	10/08/08	   15				  False
001	10/09/08				  2		 False
002	10/09/08				  1		 False
------------------------------------------------------------------------

How can I make an SQL Query to return a ROW like this?

ITEM_CODE  DESCRIPTION  TOTAL_IN TOTAL_OUT	REMAINING
--------------------------------------------------------------------------

--------------------------------------------------------------------------

***Posted = False

[Hary]

You can get a result by writing the correct query and reading your homework. Please post a strart of what you think the solution is. We're glad to help with your homework, but we won't make it completely.

[porcupine75]

Hary, on 5 Nov, 2008 - 04:39 AM, said:

You can get a result by writing the correct query and reading your homework. Please post a strart of what you think the solution is. We're glad to help with your homework, but we won't make it completely.

Thanks bro, I love that... Rules are rules...

This ain't homework, I do simple freelance job using vb 6. My apology...
I am not good in SQL, I am doing this by old style (or somehow THE FALSE STYLE)...
1. I make a function to return the records/rows of all the ITEMS (simple SELECT STATEMENTS).
2. Store the returned records/rows in my USER DEFINED TYPES array .
3. I then run a loop on all the records/rows, this time I make a function again that use the "SUM" statement (actually 2 functions-1 for STOCK IN and 1 for STOCK OUT).
4. Store 'em again in my USER DEFINED TYPES array...

Bad... OLD FASHIONED... THE FALSE STYLE... just need help for further improvement per se... thanks...

I love you guys...

[Hary]

SELECT i.itemCode, i.Description, SUM(l.StockIn) AS TotalIn, SUM(itemLedger.StockOut) AS TotalOut FROM itemList i JOIN itemLedger l ON i.item_code = l.item_code WHERE l.posted = FALSE GROUP BY l.itemCode

* I didnt test, not 100% sure it works

This post has been edited by Hary: 05 November 2008 - 10:43 AM

[eclipsed4utoo]

Hary, on 5 Nov, 2008 - 12:43 PM, said:

SELECT i.itemCode, i.Description, SUM(l.StockIn) AS TotalIn, SUM(itemLedger.StockOut) AS TotalOut FROM itemList i JOIN itemLedger l ON i.item_code = l.item_code WHERE l.posted = FALSE GROUP BY l.itemCode

* I didnt test, not 100% sure it works

a little addition. I didn't test either but should work.

SELECT
     I.ItemCode
     , I.Description
     , SUM(IL.StockIn) AS 'TotalIn'
     , SUM(IL.StockOut) AS 'TotalOut'
     , (SUM(IL.StockIn) - SUM(IL.StockOut)) AS 'Remaining'
FROM ItemList I, ItemLedger IL
WHERE I.ItemCode = IL.ItemCode AND IL.Posted = 'False'
GROUP BY I.ItemCode

[Hary]

Carthesian product? What a waste of resources... use a 'normal' join.

For even better performace, I forgot the remaining, sorry:

SELECT i.itemCode, 
       i.Description, 
       SUM(l.StockIn) AS TotalIn, 
       SUM(itemLedger.StockOut) AS TotalOut,
       (TotalIn - TotalOut) AS Remaining
FROM itemList i JOIN itemLedger l 
ON i.item_code = l.item_code WHERE l.posted = FALSE 
GROUP BY l.itemCode

This way you do not have to recalculate the Sums

This post has been edited by Hary: 05 November 2008 - 11:32 AM

[eclipsed4utoo]

Hary, on 5 Nov, 2008 - 01:31 PM, said:

Carthesian product? What a waste of resources... use a 'normal' join.

For even better performace, I forgot the remaining, sorry:

SELECT i.itemCode, 
       i.Description, 
       SUM(l.StockIn) AS TotalIn, 
       SUM(itemLedger.StockOut) AS TotalOut,
       (TotalIn - TotalOut) AS Remaining
FROM itemList i JOIN itemLedger l 
ON i.item_code = l.item_code WHERE l.posted = FALSE 
GROUP BY l.itemCode

This way you do not have to recalculate the Sums

sorry, but the (TotalIn - TotalOut) doesn't work in SQL Server 2005.

also, this..

SELECT *
FROM Orders 0, Customers C
WHERE C.CustID = O.CustID

is EXACTLY the same as

SELECT *
FROM Orders 0 
    INNER JOIN Customers C ON  O.CustID = C.CustID

and EXACTLY the same as

SELECT *
FROM Orders 0 
    JOIN Customers C ON  O.CustID = C.CustID

The way that I posted is actually a normal way of doing an inner join in Oracle. If you are just going to do an inner join, the way that I posted is just as fine as typing "INNER JOIN"(or JOIN). Performance is EXACTLY the same.

You also have a misunderstanding of what a Cartesian join is.....or you misread my query.

This post has been edited by eclipsed4utoo: 05 November 2008 - 11:42 AM

[Hary]

Uhm, those queries do generate the same result, but they do it in a different way. The latter ones do profit more from indexes, as the are specified in the join. In the first one, the DBMS optimizer has to decide it uses the indices from the where clause, where there is a chance it doesn't. But, for simple queries, it is the same, that's true.

But I doubt it's worth a discussion: the OP's qustion is answered I guess.

This post has been edited by Hary: 05 November 2008 - 11:53 AM

[eclipsed4utoo]

Hary, on 5 Nov, 2008 - 01:52 PM, said:

Uhm, those queries do generate the same result, but they do it in a different way. The latter ones do profit more from indexes, as the are specified in the join. In the first one, the DBMS optimizer has to decide it uses the indices from the where clause, where there is a chance it doesn't. But, for simple queries, it is the same, that's true.

But I doubt it's worth a discussion: the OP's qustion is answered I guess.

SQL Server's optimizer is beautiful at optimizing the query either way. Putting the join in the WHERE clause is Microsoft's way of joining.

as long as you are doing an inner join, there is no reason, other than personal preference, to use one method or the other. both have the same performance and both will return the same data.

[porcupine75]

wooooooooot wwwwwwwoooooooooootttt!!!

G.G.GREAT CODER'S 'N 'DA HAUS!!!

JOIN statement do help, and I missed the GROUP BY w/c was vital.

I'll say it again! G.G.GREAT CODER'S 'N 'DA HAUS!!!

Thanks Guys (Hary & eclipsed4utoo)...