Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 19th, 2004, 03:10 AM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 58
Thanks: 0
Thanked 0 Times in 0 Posts
Default Unexpected result from the query


I have 4 tables with the following structure.

Colors:

color_code number(PK)
color_name Text

transfermain:

trindno Autonumber(PK)
job_code Text
factory_code Text
trdate Date/Time

Transfersub:

transid Autonumber(PK)
trindno Number(FK from transfermain)
color_code Number(FK)
qty Number

Dailyoutput:

indexno Autonumber(PK)
transid Number(FK from transfersub)
Qty Number
trdate Date/time

The data is like

colors:

color_code Color_name
---------------------------
1 Green
2 Combo
3 Chi chi combo
4 Navy

Transfermain:

trindno job_code fact_code
-----------------------------------------
1 04A001 ATH
2 04A001 DFB
3 04A002 PKS
4 04A001 MON

transfersub:

transid trindno color_code Qty
-----------------------------------
1 1 2 3000
2 1 3 2000
3 2 2 2500
4 2 3 3000
5 2 4 4500
6 3 2 1500
7 4 1 1000

dailyoutput:

indexno transid qty
----------------------------
1 1 3000
2 3 1000
3 4 2000
4 2 2000
5 5 3000


Means, the same job is assigned to different factories with same/diff colors and quantities. In the dailyoutput table, color for each factory has only one record. Its a cumulative quantity.

I want to display the resport to show all colors for the job(input) and quantity(sum of qty group by color_code from transfersub as assignedqty) and quantity(sum of quantity for each color from dailyoutput as finishedqty).

Ex: Report for job 04A001
--------------------------

color_name Assigned Qty Finished Qty
--------------------------------------------
Combo 7000 4000
Chi chi combo 6000 4000
Navy 1000 0


I have given the query as

SELECT colors.color_name, transfersub.qty, dailyoutput.qty
FROM colors RIGHT JOIN ((transfermain RIGHT JOIN transfersub ON transfermain.trindno = transfersub.trindno) LEFT JOIN dailyoutput ON transfersub.transid = dailyoutput.transid) ON colors.color_code = transfersub.color_code
WHERE (((transfermain.job_code)="04A001"));

It is displaying the same color more than once(coz, the same color can be assigned to different factories). I want to sum up all the quantities for each color.

How can i get the output as i needed.



Reply With Quote
  #2 (permalink)  
Old August 23rd, 2004, 03:50 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default


SELECT colors.color_name, SUM(transfersub.qty) as SumofTrans, SUM(dailyoutput.qty) AS SumofDailyOut
FROM colors RIGHT JOIN ((transfermain RIGHT JOIN transfersub ON transfermain.trindno = transfersub.trindno) LEFT JOIN dailyoutput ON transfersub.transid = dailyoutput.transid) ON colors.color_code = transfersub.color_code
GROUP BY colors.color_name
WHERE (((transfermain.job_code)="04A001"));


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrong result on select query penta Access 3 May 5th, 2005 09:05 AM
Show result from query akibaMaila VB.NET 2002/2003 Basics 2 January 18th, 2005 12:43 PM
XSLT: unexpected result saurabh0 XSLT 4 November 2nd, 2004 01:21 PM
Query Result mateenmohd SQL Server 2000 0 November 1st, 2003 03:53 AM
Archiving query result Ned SQL Server 2000 5 October 8th, 2003 03:34 PM



All times are GMT -4. The time now is 08:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.