Need help with multiple query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with multiple query

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2011-04-05 : 12:58:37

I have the need to pull data from three tables. Two tables are going to have aggregate functions. My first query works, but when I join in the second aggregate, then my numbers are incorrect.

Here is one version of the query where my On Hand Qty is incorrect (I believe it sums up for every record I have in APFVT, which is incorrect):


SELECT PM.PART_ID, PM.STD_UNIT_COST, SUM(SS.ON_HAND_QTY) AS "ON HAND QTY", MAX(VT.VOUCHER_ID) AS "VOUCHER ID"
FROM ICFPM PM, ICFSS SS, APFVT VT
WHERE PM.PART_TYPE = 'B' AND SS.ON_HAND_QTY > 0 AND (PM.PART_ID = SS.PART_ID)  AND (PM.PART_ID = VT.PART_ID)
GROUP BY PM.PART_ID, PM.STD_UNIT_COST

The quantity on the first item should be 76, but I get 1444. There is a 1 - many relationship between ICFPM and ICFSS as well as ICFPM and APFVT.

Here is another version of the query that I am trying, but in this case I get two records, both with half the information I need. If I could get this to produce 1 record for each Part ID with the corresponding information that would be great.


SELECT VT.PART_ID, CONVERT(INT, '') AS "UNIT COST", CONVERT(INT, '') AS "ON HAND QTY", MAX(VT.VOUCHER_ID) AS "VOUCHER ID"
FROM APFVT VT
WHERE VT.PART_ID <> ''
GROUP BY VT.PART_ID
    
UNION
  
SELECT PM.PART_ID, PM.STD_UNIT_COST, SUM(SS.ON_HAND_QTY) AS "ON HAND QTY", ''
FROM ICFPM PM LEFT OUTER JOIN ICFSS SS ON PM.PART_ID = SS.PART_ID
WHERE PM.PART_TYPE = 'B' AND SS.ON_HAND_QTY > 0 AND (PM.PART_ID = SS.PART_ID)
GROUP BY PM.PART_ID, PM.STD_UNIT_COST

Here is what I get:


PART_ID 	UNIT COST	ON HAND QTY	VOUCHER ID
180-001202     	0.000000	0.0000	        0011030144104
180-001202     	133.710000	76.0000

Here is what I would like to get.


PART_ID	       UNIT COST	ON HAND QTY	VOUCHER ID
180-001202     133.710000	76.0000	        0011030144104

Any help would be greatly appreciated. I have tried dozens of variations and can not hit the exact statement I need.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 13:48:37

could you please provide sample APFVT, ICFPM , ICFSS data for part_id 180-001202 that matches your query filters?


declare @APFVT table(PART_ID varchar(50), STD_UNIT_COST decimal(10,2))
Insert Into @APFVT
SELECT '',
       50.00

declare @ICFPM table()
declare @ICFSS table()

That would help

If you don't have the passion to help people, you have no passion

texassynergy
Starting Member

26 Posts

Posted - 2011-04-05 : 14:15:26

Thanks yosiasz.

Putting the data into temp tables makes sense. That way I can get unique values into the two temp tables that have the aggregate columns. I appreciate your help. I don't use temp tables a lot and keep forgetting that they are a valuable tool.
Once again, thanks alot for your help. You just jump started my process.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 14:19:31

texasranger i was not suggesting to put in temp table. i was asking you to send us sample data :)

If you don't have the passion to help people, you have no passion

texassynergy
Starting Member

26 Posts

Posted - 2011-04-05 : 14:31:31

yosiasz,
I know what you were asking. However, you showed me the syntax for temp tables and that reminded me that I could use them. That way the APFVT and ICFSS queries could be loaded into those tables which provides me distinct records. Now I have a 1 to 1 relationship with all three tables.

You did help, without even providing much details. Again, mucho appreciado.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 14:53:45

cool! denada muchacho!

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources