Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 19th, 2004, 03:10 AM
Authorized User
 
Join Date: Aug 2004
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.



 
Old August 23rd, 2004, 03:50 AM
Friend of Wrox
 
Join Date: Sep 2003
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"));







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





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