Return vehicles that have all parts in a given table

Question

I have the following question, in my database I have the following tables:

|      veiculo      |
| idVeiculo | placa |

|        peca       |
| idPeca | nomePeca |

|      relVeiPec     |
| idVeiculo | idPeca |

| pecaObrig |
|   idPeca  |

I need a SELECT on the table relVeiPec returning vehicles that have all table parts pecaObrig.

I’ve tried it with the clause IN, but it returns the vehicle it has whichever table piece pecaObrig, I wanted you to return the vehicle you would have all the pieces of this table and not any one.

SELECT vp.idVeiculo, v.placa, vp.idPeca, p.nomePeca FROM relVeiPec AS vp
INNER JOIN veiculo AS v ON v.idVeiculo = vp.idVeiculo
INNER JOIN peca AS p ON p.idPeca = vp.idPeca
WHERE vp.idPeca IN (SELECT idPeca FROM pecaObrig) 

Answer 1

SQL:

SELECT v.* FROM pecaobrig c
RIGHT JOIN relveipec a on a.idPeca = c.idPeca 
INNER JOIN veiculo v on v.idVeiculo=a.idVeiculo
WHERE not(c.idPeca is null)
GROUP BY a.idVeiculo 
HAVING count(a.idVeiculo) = (SELECT count(idPeca) FROM pecaobrig)

To optimize create a StoredProcedure

DELIMITER $$

CREATE PROCEDURE `test1`.`VeiculosComPecasObrigatorias` ()
BEGIN

    DECLARE qtde int;
    SET qtde = (SELECT count(idPeca) FROM pecaobrig);

    SELECT v.* FROM pecaobrig c
    RIGHT JOIN relveipec a on a.idPeca = c.idPeca 
    INNER JOIN veiculo v on v.idVeiculo=a.idVeiculo
    WHERE not(c.idPeca is null)
    GROUP BY a.idVeiculo 
    HAVING count(a.idVeiculo) = qtde;          
END

Answer 2

The query below would be an example of how to do this.

--|      veiculo      |
--| idVeiculo | placa |

--|        peca       |
--| idPeca | nomePeca |

--|      relVeiPec     |
--| idVeiculo | idPeca |

--| pecaObrig |
--|   idPeca  |

Declare @Veiculos table
(     
    idVeiculo int,  
    placa varchar(7)
);

insert into @Veiculos values 
(1,'AAA1111'), 
(2,'BBB2222'), 
(3,'CCC3333'), 
(4,'DDD4444');

Declare @Peca table
(     
    idPeca int,     
    nomePeca varchar(20)
);

insert into @Peca values 
(1,'porta'),  
(2,'vidro'), 
(3,'pneu'), 
(4,'Motor'), 
(5,'chassis'),  
(6,'parabrisa');

Declare @pecaObrig table 
(   
    idPeca int
);

insert into @pecaObrig values 
(1), 
(4), 
(5);

Declare @relVeiPec table
(    
    idVeiculo int, 
    idPeca int
)

insert into @relVeiPec values (1,1) ,(1,2) ,(1,3) ,(1,4) ,(1,5),(1,6),
(2,1) ,(2,2) ,(2,6),(3,1) ,(3,2),(3,4) ,(3,6),(4,1) ,(4,4) ,(4,5);

SELECT vp.idVeiculo, v.placa, vp.idPeca, p.nomePeca 
    FROM @relVeiPec AS vp
    INNER JOIN @Veiculos AS v 
        ON v.idVeiculo = vp.idVeiculo
    INNER JOIN @Peca AS p 
        ON p.idPeca = vp.idPeca
        WHERE v.idVeiculo in (
                    select idVeiculo 
                        from @pecaObrig po
                        join @relVeiPec rcp
                        on rcp.idPeca = po.idPeca
                        group by idVeiculo
                        having count(rcp.idPeca)  = (select count(*) from @pecaObrig)
                    )

Or you can use the EXISTS which is more efficient than IN

SELECT vp.idVeiculo, v.placa, vp.idPeca, p.nomePeca 
FROM @relVeiPec AS vp
INNER JOIN @Veiculos AS v ON v.idVeiculo = vp.idVeiculo
INNER JOIN @Peca AS p ON p.idPeca = vp.idPeca
WHERE EXISTS  (
    select idVeiculo from @pecaObrig po
    join @relVeiPec rcp
    on rcp.idPeca = po.idPeca
    where idVeiculo = vp.idVeiculo
    group by idVeiculo
    having count(rcp.idPeca)  = (select count(*) from @pecaObrig)