SQL WHERE look for 2 data in one column

I got 2 tables

item:

name
id
other

part:

name_part
id
other

I wish to make a query to find what item is linked with name_part = ‘a’ AND name_part = ‘b’

2 different parts in a same item

Query:

SELECT item.name
FROM item
INNER JOIN table1 ON item.id = part.id
WHERE part.name_part = 'a' AND part.name_part = 'b'

The output us ‘0’

Maybe with nested WHERE ?

I’ve tried, but I can’t find how to make it work.

PS: I’ve already ask for a similar question but it’s was very imprecise.

Answer

Forpas’s solutions are very reasonable. This answer, though, extends your attempt.

You can do what you want with joins, but you need two of them:

SELECT i.name
FROM item i JOIN
     part pa
     ON pa.id = i.id  AND
        pa.name_part = 'a' JOIN
     part pb
     ON pb.id = i.id AND
        pb.name_part = 'b';

Your version cannot work, because name_part cannot be both 'a' and 'b' in the same row.