Orphan records , checking in another table

Asked

Viewed 1,050 times

2

I have a table of photos:

 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. (orphan photos) probably the product has been deleted and the photo not.

select  int_IDProduto  from tbl_fotos
where  int_IDProduto not in (Select int_IDfrom tbl_produtos)

this right? it seems to me it brings more results than I imagined.

sql-server

2 answers

1

It must be because you repeat the records.

To find out 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 amount of orphaned photos, and only the amount, use:

select count(*)
from tbl_fotos
where int_IDProduto not in (Select int_ID from tbl_produtos)
  • 1

    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.

  • Nothing to do. Where he asked for quantity?

  • 2

    @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 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.

  • @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.

  • 1

    I edited the answer, since the previous query produces different results than what it requested.

  • @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.

  • 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.

Show 3 more comments

1


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.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.