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)
 Bill of materials query

Author  Topic 

Ristos85
Starting Member

8 Posts

Posted - 2013-12-09 : 05:36:41
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

52326 Posts

Posted - 2013-12-09 : 06:44:25
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 - 2013-12-09 : 07:56:30
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

52326 Posts

Posted - 2013-12-09 : 08:41:10
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 - 2013-12-09 : 09:10:08
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

52326 Posts

Posted - 2013-12-09 : 09:39:20
[code]
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
[/code]


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

Ristos85
Starting Member

8 Posts

Posted - 2013-12-10 : 01:48:01
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 - 2013-12-10 : 03:43:11
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

52326 Posts

Posted - 2013-12-10 : 05:43:30
Cool

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

- Advertisement -