SQL Query return 0 rows

Question

Material table :

**ID_Material(PK)**         **Material_Name**      **Type**
DXTR1000                    Deluxe Touring Bike     Finished Good
CHAN1000                    Chain                   Raw
BOLT3000                    Socked Bolt             Raw
TRTR1000                    Touring Tire            Semi Finished

---

Detail_Material Table

**ID_Material (PK,FK)**    **ID_Material_MadeOf (PK,FK)**  **Qty**
DXTR1000                     CHAN1000                       1
DXTR1000                     TRTR1000                       5
DXTR1000                     BOLT1000                       2

---

i want the query output to show list of materials needed to make an finished good.

i made this query :

SELECT *
FROM material m
INNER JOIN detail_material d ON (m.id_material = d.id_material)
WHERE m.id_material IN (
        SELECT id_material_madeof
        FROM detail_material
        WHERE id_material = 'DXTR1000'
        );

it return nothing

Answer 1

If you want to know, which materials are need for building DXTR1000 then this query will do:

SELECT  m.ID_Material, m.Material_Name, m.[Type]
FROM    Detail_Material dm
        INNER JOIN Material m ON dm.ID_Material_MadeOf = m.ID_Material
WHERE   dm.ID_Material = 'DXTR1000'

Your query returns nothing, because your query says "give me all materials which need DXTR1000 to be built" ==> and there are none!

Answer 2

Just run the query in parts and you'll see the problem. This query:

select *
from material m join
     detail_material d
     on m.id_material = d.id_material

Is going to return this row three times, once for each row in the second table:

DXTR1000      Deluxe Touring Bike     Finished Good

The value of m.id_material is DXTR1000. I don't see that value anywhere in the ID_Material_MadeOf column. There is no match, so there are no rows.

Clearly, this query does not do what you want. Ask another question with sample data and desired results, and perhaps someone can help you with your real problem. Editing this question is impolite to people who have answered and commented on the original version of the question.