Problems in a query with related tables

You can make the query with several subqueries, making a NATURAL JOIN between them. For example the Products that have 384 or 379 in the query A, and on the other hand the products that have 377 in the query B. Once you have these characteristics and doing the NATURAL JOIN you will keep the elements of both queries that meet the condition (I understand what you want).

How they have told you the 'caracteristicas.idvalor' can not have two values at the same time so you will have to use aliases to designate two or more characteristic tables.

I am not an expert in SQL and I have the following problem ... I have two "related" tables, one of products and another of product characteristics in which I keep values that these products can have, for example: colors, materials , sizes, etc ...

What I am doing is a filter that pulls out products according to the selected characteristics but I have the problem when it comes to extracting the results with the following sentence:

SELECT 
    productos.ref,productos.nombre,caracteristicas.ref,caracteristicas.idvalor 
FROM productos 
LEFT JOIN 
    caracteristicas ON (caracteristicas.ref=productos.ref)  
WHERE 
    (caracteristicas.idvalor = 384 OR caracteristicas.idvalor = 379 ) 
and   exists (select 1 from caracteristicas as cx where cx.idvalor = 377 and cx.ref =  caracteristicas.Ref) 
ORDER BY 
    productos.nombre ASC

Before I had it all with a %code% style ... %code% what I do not want to remove the products that have those characteristics, but the query is ...

Products that have 384 or 379 and also have 377.

I do not know if I explain myself?

The query does not give an error, but it does not give me results when there are records that meet those conditions.

Any help?

Thanks in advance.

You can make the query with several subqueries, making a NATURAL JOIN between them. For example the Products that have 384 or 379 in the query A, and on the other hand the products that have 377 in the query B. Once you have these characteristics and doing the NATURAL JOIN you will keep the elements of both queries that meet the condition (I understand what you want).

How they have told you the 'caracteristicas.idvalor' can not have two values at the same time so you will have to use aliases to designate two or more characteristic tables.

Starting from your same query, with a exists you can do it.

SELECT 
    productos.ref,productos.nombre,caracteristicas.ref,caracteristicas.idvalor 
FROM productos 
LEFT JOIN 
    caracteristicas ON (caracteristicas.ref=productos.ref)  
WHERE 
    (caracteristicas.idvalor = 384 OR caracteristicas.idvalor = 379 ) 
and   exists (select 1 from caracteristicas as cx where cx.idvalor = 377 and cx.ref =  caracteristicas.Ref) 
ORDER BY 
    productos.nombre ASC