Select between 3 tables, 5 layers deep - need help on Query

Transact-SQL
1

I have three tables examples below. I want to pull in all Material and Qty on tbl_transactions, but if the material exists on tbl_master_kits, I want to return the Component and Qty (but multiple the Qty by the Qty of the Material on tbl_transactions)...but...as you can see sometimes there is a kit in a kit...any help on this would be appreciated!

tbl_transactions
ID/Material/Qty
1 / Part_A / 1
2 / Part_B / 2
3 / Part_A / 1
4 / Part_C / 5
5 / Part_B / 1

tbl_master_kits
Material / Bill
Part_A / 1000
Part_B / 1001
Part_D / 1003
Part_E / 1004

tbl_kit_components
Bill / Component / Qty
1000 / C_1 / 1
1000 / C_2 / 15
1000 / Part_B / 25
1000 / C_4 / 2
1001 / C_3 / 1
1001 / C_4 / 5
1002 / C_5 / 3
1002 / C_6 / 5
1002 / Part_C / 1
1003 / C_3 / 2
1003 / C_5 / 1
1003 / C_7 / 1

2

can you provide the desire output as well?

3

This sounds like a full BOM explosion. That will require either a full set of code or very complex recursive code.