Combine Queries into single one

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

General SQL Server Forums

New to SQL Server Programming

Combine Queries into single one

Author

Topic

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 16:36:18

I want to combine the following queries:

a.
---------------Production BOM---------------------------------------
--Select
-- [Production BOM No_]
-- ,[NO]
-- ,[Quantity]
-- ,[Unit of Measure Code]
-- From BOM

b.Combine BOM and Item Ledger using BOM.[Production BOM No_]= ItemLedger.[Item No_]

-----------------------Item Ledger Entry----------------------------
--SELECT
--
-- [Item No_]
-- ,sum([Quantity]) as QuantityOnHand
--FROM ItemLedger
--Group By [Item No_]

c. Combine BOM and Purchase using BOM.[No_]= Purchase.[No_]

-----------------------Purchase Line Table---------------------------
--SELECT
-- [No_]
-- ,[Unit of Measure]
-- ,sum([Quantity])as QuantityOnPurchaseOrder
--
-- FROM Purchase
-- Where [Document Type]=1

-- Group By [No_],[Unit of Measure]

I would like a result in a single table combing all the queries.Waiting for suggestions.

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 16:37:04

Show us some sample data for each of your queries, and what the output should look like all combined with that sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:15:44

a. Query (a.) results
Production BOM No_ No_
B085A-D1012-075 B085A-00000-075
B085A-D1012-105 B085A-00000-105
N938A-D4005-180 C002A-00000-180
N252A-00000-200 C003A-00000-200
N252A-D1006-200 C003A-00000-200

b.Query (b.) results

Item No_ QuantityonHand
ZBL-1159-10 93.00000000000000000000
R335I-00000-095 2.00000000000000000000
DSCVB-P0000-000 10.00000000000000000000
A601O-DV024-000 1.00000000000000000000
MV-1821227-105 25.00000000000000000000
SK-R122V-00000-100 0.00000000000000000000

c. Query (c.) results
No_ QuantityonPurchaseOrder
ZSK-7001CO-13.5 5.00000000000000000000
A171U-00000-000 8.00000000000000000000
R269S-00000-060 1.00000000000000000000
ZBL-3649B-10 98.00000000000000000000
ZTF-1002-100 2993.00000000000000000000
E103S-07913-000 2.00000000000000000000
A063U-07750-000 23.00000000000000000000

Now i want to use this result and create a query where

BOM.NO_= Sales.[Item.No_]

BOM.NO_ = Purchase.[No]

Thanks

SAROJ

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:17:02

Sorry. lets make it small. So i would like to work on combining 3 queries above.

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:18:11

So how should it look if you combine all 3 using the above data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:24:27

BOM.NO_ [Item.No_] Purchase.[No]
x y z

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:25:13

That doesn't make sense. I can't help you unless you provide the requested information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:26:03

Production BOM No_ NO_ QuantityonHand QuantityonPurchaseOrde
x y 0 0
Sorry this is correct format

SAROJ

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:28:04

B085A-D1012-075 B085A-00000-075 10.00000000000000000000 8.00000000000000000000
sorry to make you confused.

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:28:17

I give up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 17:49:10

Sorry Tara to bring this confusion again
I would like to get 4 columns from the result
1. Production BOM No_
2. NO_
3. QuantityonHand
4. QuantityonPurchaseOrder

Using

BOM.NO_= Sales.[Item.No_]

BOM.NO_ = Purchase.[No

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 17:57:38

You need to provide the exact output you need given the sample data you provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 18:07:07

Perhaps you're after something like this:


SELECT b.[Production BOM No_],
		b.NO_,
		i.QuantityonHand,
		p.QuantityonPurchaseOrder
FROM BOM b
INNER JOIN (
		SELECT
		[Item No_], sum([Quantity]) as QuantityOnHand
		FROM ItemLedger
		GROUP BY [Item No_]) i
	ON  b.[Production BOM No_]= i.[Item No_]
INNER JOIN (
		SELECT [No_] ,sum([Quantity]) as QuantityOnPurchaseOrder
		FROM Purchase
		WHERE [Document Type]=1
		GROUP BY [No_]) p
	ON  b.[No_] = p.[No_]

There are 10 types of people in the world, those that understand binary, and those that don't.

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 18:09:33

Sample Data

[Production BOM No_] [No_]
R500 ZSK-100
R600 ZSK-200
R700 ZSK-300
R800 ZSK-400
R900 ZSK-500

[Item No_] [QuantityonHand]
ZSK-200 50
ZSK-400 60
ZSK-100 70

[No_] [QuantityonPurchaseOrder]

ZSK-100 50
ZSK-200 80
ZSK-300 90
ZSK-400 70

Result I needed

[Production BOM No_] [No_] [QuantityonHand] [QuantityonPurchaseOrder]
R500 ZSK-100 70 50
R600 ZSK-200 50 80
R800 ZSK-400 60 70

SAROJ

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 18:36:53

GROUP BY [Item No_]) i
In above statement I could not understand what is 'i' after the closing parenthesis. Would you please explain it to me?

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 18:42:46

It's a derived table, sort of like a view but inside a query. The "i" is to alias the derived table so that you can reference the columns in the outer query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sneupane
Starting Member

28 Posts

Posted - 2010-04-06 : 18:46:22

woo thanks DBA in the making. I did it by your suggestion. You are the smarter one.

SAROJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-06 : 18:50:06

He's quite the SQL person. Glad to have you here, DBA in the making!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 19:18:06

Awww shucks. Thanx guys.