That’s right. You don’t need the distinct
, there is no reason to duplicate result (unless there is the possibility of having equal photos in this table with different ids, which is the problem of having an unnatural PK). If you just 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 none, you should probably have FK’s 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, by chance, is that there are no photos that are not linked to products, in addition to creating the above Constraint you should pass the column not to accept null:
ALTER TABLE tbl_fotos ALTER COLUMN int_IDProduto INT NOT NULL;
In any case, to know the photo Ids just replace count(1)
for int_ID
.
I voted against because this query produces a different result than he asked. There you are bringing the invalid product ids in the photo table (and, together, the amount of PRODUCTS). This is quite different than bringing the amount of PHOTOS that was requested.
– Pedro Lorentz
Nothing to do. Where he asked for quantity?
– Leonel Sanches da Silva
@Pedrolorentz Although the OP have asked "how many photos exist without products", it is evident that he wants to know which ones to take action, such as removing them.
– utluiz
@utluiz and Cigano, the error persists because the distinct is being done on the product id. This will not give either how many pictures and what are they.
– Pedro Lorentz
@Pedrolorentz My intention is just to clarify things. The way it is the query returns which products no longer exist. And, Gypsy, with the last edition was a very complete answer. As soon as I can again vote I will positive both answers.
– utluiz
I edited the answer, since the previous query produces different results than what it requested.
– Leonel Sanches da Silva
@utluiz, includes the last line of my answer now so that she starts talking about being able to change Count(1) by int_ID to bring the photo ids. I hadn’t really put this in before because it was a simple change that I assumed the OP would be able to do if he wanted the Ids. Thanks for the improvement advice.
– Pedro Lorentz
Gypsy, the distint remains unnecessary in the second select, note that int_ID is PK of the table. The answer to the final question is still wrong here: "This right? seems to me to bring more results than I imagined." and you answered with "It must be because you repeat the records." Which is not true, the amount of records returned is right.
– Pedro Lorentz