CODEWITHSUNDEEP
sqlselectsubquerydistinct
Marco
Marco

Reputation: 25

SQL - Distinct subquery

Someone can explain how to do this query:

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

That's the table

That's my not working query:

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

Upvotes: 1

Views: 18790

Answers (3)

Aravindhan R
Aravindhan R

Reputation: 269

You can't use the distinct in the sub query. Instead of that you can do like this:

Select distinct column names,.. from table1 where column name= select statement;

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Do it like this :

SELECT riempie1.* FROM riempie1
join (select idProdotto from riempie1 group by riempie1 having count(riempie1)=1) riempie2
on riempie1.idProdotto = riempie2.idProdotto

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 8

Related Questions