SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Bill of materials query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ristos85
Starting Member

8 Posts

Posted - 12/09/2013 :  05:36:41  Show Profile  Reply with Quote
Hello,

I have a BOM table with all finished item receipes and semi items receipes. What I would like help on is to create a query where semi item materials are also listed in finished item receipe. Please see link for sample.
I could also add a column ItemType where 1 means that it's material and 2 means it's semi item
http://web.zone.ee/ristos85/bomquery.jpg

Any ideas/hints how to create this?

best regards,
Risto

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  06:44:25  Show Profile  Reply with Quote
do you mean this?

SELECT COALESCE(t1.BOMNo,t.BOMNo) AS BOMNo,
t.MaterialNo,
t.MaterailDescription,
t.Quantity
FROM BomTable t
LEFT JOIN BOMTable t1
ON t.BOMNo = t1.MaterialNo


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ristos85
Starting Member

8 Posts

Posted - 12/09/2013 :  07:56:30  Show Profile  Reply with Quote
This is exactly what I was looking for. Thank you visakh16, you saved my day

quote:
Originally posted by visakh16

do you mean this?

SELECT COALESCE(t1.BOMNo,t.BOMNo) AS BOMNo,
t.MaterialNo,
t.MaterailDescription,
t.Quantity
FROM BomTable t
LEFT JOIN BOMTable t1
ON t.BOMNo = t1.MaterialNo


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  08:41:10  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ristos85
Starting Member

8 Posts

Posted - 12/09/2013 :  09:10:08  Show Profile  Reply with Quote
One last question. What if I have three levels. Like in following picture http://web.zone.ee/ristos85/bomquery2.jpg

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/09/2013 :  09:39:20  Show Profile  Reply with Quote

SELECT COALESCE(t2.BOMNo,t1.BOMNo,t.BOMNo) AS BOMNo,
t.MaterialNo,
t.MaterailDescription,
t.Quantity
FROM BomTable t
LEFT JOIN BOMTable t1
ON t.BOMNo = t1.MaterialNo
LEFT JOIN BOMTable t2
ON t.BOMNo = t2.MaterialDescription



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ristos85
Starting Member

8 Posts

Posted - 12/10/2013 :  01:48:01  Show Profile  Reply with Quote
Almost. The third level article is listed nicely as article 0ABC material but materials that are in the third level article are not listed. How to get these materials (foil4, foil5) also below article 0ABC?

http://web.zone.ee/ristos85/bomquery3.jpg




quote:
Originally posted by visakh16


SELECT COALESCE(t2.BOMNo,t1.BOMNo,t.BOMNo) AS BOMNo,
t.MaterialNo,
t.MaterailDescription,
t.Quantity
FROM BomTable t
LEFT JOIN BOMTable t1
ON t.BOMNo = t1.MaterialNo
LEFT JOIN BOMTable t2
ON t.BOMNo = t2.MaterialDescription



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

Ristos85
Starting Member

8 Posts

Posted - 12/10/2013 :  03:43:11  Show Profile  Reply with Quote
Got it working!

SELECT COALESCE (t2.BOMNo, t1.BOMNo, t.BOMNo) AS BOMNo, t.MaterialNo,
t.MaterialDescription,
t.Quantity
FROM dbo.BomTable AS t
LEFT OUTER JOIN
dbo.BomTable AS t1 ON t.BOMNo = t1.MaterialNo
LEFT OUTER JOIN
dbo.BomTable AS t2 ON t1.BOMNo = t2.MaterialNo
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/10/2013 :  05:43:30  Show Profile  Reply with Quote
Cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000