есть 2 таблицы
products(id, name)
specs(product_id, spec_id, spec_value)

specs.product_id = products.id

у каждого продукта может быть произвольное кол-во спеков.

нужно выбрать id продуктов у которых есть спек с id=A и значение этого спека, но нет спека с id=B

вот что написал

SELECT id, spec_a FROM 
(SELECT id, t_spec_a.spec_value spec_a
FROM `products` 
LEFT JOIN specs t_spec_a
ON (t_spec_a.product_id=products.id AND t_spec_a.spec_id='A')
WHERE t_spec_a.spec_value IS NOT NULL) t1
LEFT JOIN specs t_spec_b
ON (t_spec_b.product_id=t1.id AND t_spec_b.spec_id='B')
WHERE t_spec_b.spec_value IS NULL

изначально хотел написать

SELECT id, t_spec_a.spec_value spec_a FROM 
FROM `products` 
LEFT JOIN specs t_spec_a, specs t_spec_b
ON (t_spec_a.product_id=products.id AND t_spec_a.spec_id='A' AND t_spec_b.product_id=products.id AND t_spec_b.spec_id='B')
WHERE t_spec_a.spec_value IS NOT NULL AND t_spec_b.spec_value IS NULL

но даже если убрать все условия из WHERE этот запрос возвращает только продукты у которого есть обе спеки (А и В)

подскажите как перефразировать этот запрос без временной таблицы. объемы данных у меня небольшие так что можно оставить и так, но хотелось бы написать правильно

select product_id, spec_value
from specs
where spec_id='A'
and not(product_id in
(select distinct product_id
 from specs
 where spec_id='B'))

или

select product_id, spec_value
from specs S
left join 
(select distinct product_id from specs where spec_id='B') S2 on (S.product_id=S2.product_id)
where S.spec_id='A'
and S2.product_id is null

Здравствуйте, naf_2000, Вы писали:

а без вложенных select нельзя? или это и есть оптимальное решение?

Здравствуйте, ghostrider, Вы писали:

G>есть 2 таблицы
G>products(id, name)
G>specs(product_id, spec_id, spec_value)

G>specs.product_id = products.id

G>у каждого продукта может быть произвольное кол-во спеков.

G>нужно выбрать id продуктов у которых есть спек с id=A и значение этого спека, но нет спека с id=B

select p.id, s.spec_value
from products p
       join specs s
         on p.id = s.product_id
            and s.spec_id = 'A'
where not exists (select null
                 from specs s2
                 where s2.product_id = p.id
                       and s2.spec_id = 'B'
                 )