How to find value in related fields?

Question

SQL Server

There are three tables. List of accessories PurchaseOrder , list of all goods and accessories for them ItemList ), orders for devices SalesOrder .
The ItemList contains the fields ProdId (product ID), ChildProdId (ID of the component that is needed to collect this product, and components can also have ChildProdId , i.e. components that are needed to collect this component). The SalesOrder contains the finished product ID. In PurchaseOrderthere are components for which the search will be performed, i.e. I check whether this spare part is needed for any order, if so, I display it for which order and how many pieces. The whole point lies in the fact that, for example, there is an order for a device and it has child products ( ChildProdId ): board, case, wiring. But the board, in the composition, has transistors, and the transistors in the composition have a transistor housing. And, for example, it is the body of the transistor that is in the PurchaseOrder table , which means that I need to display it and the order number to which it is needed.
Can views solve this problem?

Answer 1

Is it possible to solve this problem somehow using SQL views?

In principle, it's even googled: https://www.google.com/search?q=recursive+sql+queries
But I wouldn't do that unless absolutely necessary. How to debug and refactor? How to transfer from MSSQL to another DBMS? Although for the educational project all this is not critical. In real life, such formulations of tasks should not exist.

and the transistors in the composition have a transistor housing

a transistor crystal contains atoms of silicon, arsenic, oxygen, etc., atoms contain protons, neutrons and electrons. The proton consists of two u-quarks and one d-quark, the neutron consists of two d-quarks and one u-quark. And here is the problem: we do not have components for collecting quarks. ;)

Answer 2

This is not a study project.

And if not educational, then there should be Parent, not Child. One product has many components, not just one. And if a component can be part of different products, then a separate table is generally needed. After bringing the structure back to normal, use a recursive query .
Or move on to Graphs: An Introduction to SQL Server Graph Databases , Processing Graphs in SQL Server .