Search data based on two columns SQL

I have a database table like this:

Id          Tipo        Provincia
RTOR-0246   INVENTARIO  MADRID
RTOR-0246   SUSTRATO    MADRID
RTOR-0247   INVENTARIO  MADRID
RTOR-0252   INVENTARIO  MADRID
RTOR-0255   SUSTRATO    MADRID
RTOR-0261   SUSTRATO    MADRID

I need search from Id the Id’s that not have Tipo = Inventario and Create a new line with that data, I mean for example, RTOR-00261 and RTOR-0255 dont have INVENTARIO, I need to create new with the fields that there is in SUSTRATO. All data are in the same table

Final outptut like this:

Id          Tipo        Provincia
RTOR-0246   INVENTARIO  MADRID
RTOR-0246   SUSTRATO    MADRID
RTOR-0247   INVENTARIO  MADRID
RTOR-0252   INVENTARIO  MADRID
RTOR-0255   SUSTRATO    MADRID
RTOR-0261   SUSTRATO    MADRID
RTOR-0255   INVENTARIO  MADRID
RTOR-0261   INVENTARIO  MADRID

I tried with:

SELECT Id 
FROM my_table 
WHERE NOT EXISTS (SELECT NULL FROM my_table WHERE id != id)

Doesn’t work

Other:

SELECT Id 
FROM my_table as t1
WHERE NOT EXISTS (SELECT Administrativo FROM my_table where Tipo='INVENTARIO') as sq
where T t1.Tipo='SUSTRATO' and t1.Id=sq.Id

Doesn’t work as well.

I need help with this – any ideas?

Answer

These two solutions work (you were very close):

With a Subquery:

SELECT `Id` 
FROM `my_table`
WHERE NOT EXISTS (SELECT 1 FROM `my_table` AS `sub` WHERE `sub`.`Id` = `my_table`.`Id` AND `sub`.`Tipo` = 'INVENTARIO');

With a left join:

SELECT `my_table`.`Id` 
FROM `my_table` LEFT JOIN
`my_table` AS `Inventario` ON (`Inventario`.`Id` = `my_table`.`Id` AND `Inventario`.`Tipo` = 'INVENTARIO')
WHERE `Inventario`.`Id` IS NULL;

DB Fiddle

Edit: I also think the question should be different. More like “How to select rows that don’t match criteria in the same table” or so. The question ‘search data based on two columns’ is way too broad, that could be answered with ‘WHERE … AND … ‘.