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

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

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

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

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources