SQL - Distinct subquery

Question

Someone can explain how to do this query:

i must select all the fields with no duplicate "idPrdodotto"

That's my not working query:

SELECT *
FROM riempie1
WHERE idProdotto IN (SELECT DISTINCT idProdotto
                     FROM riempie1
                    );

Answer 1

You appear to be learning SQL, so I'll be a bit verbose.

The SELECT DISTINCT in the IN subquery does nothing. Nothing at all. The IN implicitly does a SELECT DISTINCT because if something is in (1, 2, 3), then that something is in (1, 1, 1, 2, 2, 3).

What you want to do is to count the number of times that idProdotto appears in the table. You want the idProdotto values that only appear once.

A typical approach would use GROUP BY, COUNT(*) and HAVING.