Question:

I've got two tables. The first one hold a type and the second one the value of this type. In my example if there are more values 'john' as 'first_name' i get:single-row subquery returns more than one row

SELECT DISTINCT id FROM name WHERE id=( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

I'm not very good with sql. I should use LEFT JOIN or something like that but it's not really clear to me how i should do this.

Apart from the simple fix (using IN instead of id=), you can also use a somewhat simpler version of your query:

SELECT DISTINCT id FROM name WHERE text1='first_name' and id in ( SELECT name_id FROM value WHERE text2='john')

Since subquery [can] returns multiple values, IN is better use than =

SELECT DISTINCT id FROM name WHERE id IN ( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

IN is equivalent for OR, example:

SELECT * FROM tableName WHERE a = 4 or a = 5 or a = 6

can be written as

SELECT * FROM tableName WHERE a in (4,5,6)

The = (equal sign) is used to assign single value.

SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

This query returns more than one row, then you should use IN clause like here

SELECT DISTINCT id FROM name WHERE id IN ( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

Or if you expect only a row, you have to review your DB's logic and behaviour.

you could just change WHERE id= to WHERE id IN