SQL Statement... (help pls...)

An SQL Statement to return the rows as stated... Please Help...

Page 1 of 1

9 Replies - 968 Views - Last Post: 06 November 2008 - 09:24 AM Rate Topic: -----

#1 porcupine75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 03-September 07

SQL Statement... (help pls...)

Posted 05 November 2008 - 04:22 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Statement... (help pls...)

#2 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 05:39 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 porcupine75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 03-September 07

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 09:50 AM

View PostHary, 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...
Was This Post Helpful? 0
  • +
  • -

#4 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 10:43 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 11:27 AM

View PostHary, 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

Was This Post Helpful? 0
  • +
  • -

#6 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 11:31 AM

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

Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 11:39 AM

View PostHary, 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

Was This Post Helpful? 0
  • +
  • -

#8 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 11:52 AM

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

Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL Statement... (help pls...)

Posted 05 November 2008 - 12:14 PM

View PostHary, 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.
Was This Post Helpful? 0
  • +
  • -

#10 porcupine75  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 03-September 07

Re: SQL Statement... (help pls...)

Posted 06 November 2008 - 09:24 AM

wooooooooot wwwwwwwoooooooooootttt!!!
:rolleyes:
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)...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1