List of vehicles that are without photos

Question

I need the list of vehicles that do not have registered photos.
I have a Vehicles table, an Image table and another Type.

Example:

tbVeiculo (VeiculoId, DataCadastro, Etc...)  
tbImagemVeiculo (ImagemVeiculoId, VeiculoId, TipoImagemId, Imagem, Data, Etc...)  
tbTipoImagem ( TipoImagemId, Nome, Etc...)  

I also need to know what types of images he hasn’t entered yet.

Answer 1

You just need the clause NOT EXISTS:

SELECT *
  FROM tbVeiculo v
 WHERE NOT EXISTS(SELECT 1
                    FROM tbImagemVeiculo iv
                   WHERE iv.VeiculoId = v.VeiculoId)

And to check by image type, make a CROSS JOIN with the table tbTipoImagem:

SELECT v.*,
       ti.*
  FROM tbVeiculo v
 CROSS JOIN tbTipoImagem ti
 WHERE NOT EXISTS(SELECT 1
                    FROM tbImagemVeiculo iv
                   WHERE iv.VeiculoId = v.VeiculoId
                     AND iv.TipoImagemId = ti.TipoImagemId)

---

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery return any line, EXISTS will be VERDADEIRO, and NOT EXISTS will be FALSO

Answer 2

Hi,

Not knowing the primary and external keys becomes difficult but you can use a NOT IN something like this

SELECT veiculos.* from tbVeiculo veiculos
where veiculos.id NOT IN (SELECT imagem.id_veiculo from tbImagemVeiculo imagem join tbTipoImagem  tipo on imagem.id = tipo.id_tipo )

Answer 3

I believe that solves your problem

SELECT
  v.*
FROM tbVeiculo v
WHERE v.id NOT IN (SELECT
  i.id_veiculo
FROM tbImagemVeiculo i
INNER JOIN tbTipoImagem t
  ON i.id = t.id_tipo)