I have a photo table:
tbl_fotos
int_ID (Pk, int)
int_IDProduto (int)
str_foto (varchar(40))
and a table for products
tbl_produtos
int_ID (PK, int)
I want to know how many photos there are without products. (orphaned photos) the product has probably been deleted and the photo is not.
select int_IDProduto from tbl_fotos
where int_IDProduto not in (Select int_IDfrom tbl_produtos)
Is that right? seems to me to bring more results than I imagined.
It's all right. You do not need distinct , there is no reason to duplicate result (unless there is a possibility of having equal photos in this table with different ids, which is the problem of having an unnatural PK). If you only want to know how many, use count:
select count(1) from tbl_fotos
where int_IDProduto not in (Select int_ID from tbl_produtos);
If every photo should be linked to a valid product or to none, you should probably have the FK constraint there:
ALTER TABLE tbl_fotos ADD CONSTRAINT [FK_fotos_produtos] FOREIGN KEY(int_IDProduto)
REFERENCES tbl_produtos (int_ID);
Then, in that case, finding how many photos without products would just be
select count(1) from tbl_fotos where int_IDProduto is null;
If the idea is that there are no photos that are not linked to products, in addition to creating the above constraint you should pass the column to not accept null:
ALTER TABLE tbl_fotos ALTER COLUMN int_IDProduto INT NOT NULL;
In any case, to know the IDs of the photos, just replace count(1) with int_ID .
It must be because it repeats the records.
To know which Product Ids no longer exist, use:
select distinct int_IDProduto
from tbl_fotos
where int_IDProduto not in (Select int_ID from tbl_produtos)
To find out which photos have orphaned products, use:
select distinct int_ID, str_foto
from tbl_fotos
where int_IDProduto not in (Select int_ID from tbl_produtos)
To know the quantity of orphaned photos, and just the quantity, use:
select count(*)
from tbl_fotos
where int_IDProduto not in (Select int_ID from tbl_produtos)