Что не правильно в запросе PostgreSQL?

Question

[Valeriu Vodnicear]

есть вот такой запрос, где:
2 = Марка автомобиля
129 = Mercedes

19 = Руль
2001569 = В правой стороне

20 = Топливо
2001570 = дизель, 2001571 = бензин

и он выбирает все Mercedes, все с правым рулём, и все дизельные авто и все авто на бензине

проблема в том что он должен выбирать только Mercedes с правым рулём и с дизелем и бензином

запрос сильно упростил
если можете объясинте что пишу не так



вот структура таблицы

запрос SQL:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND pc.characteristic_id IN   (2,     19,        20)
        AND pc.selected_option_id IN  (129,   2001569,   2001570, 2001571)
     )

Answer 1

[hell]

"В его личном деле было написано - ОСОБЫЕ ПРИМЕТЫ - все" (Dellamorte Dellamore)
1) Мне кажется, что вам стоит серьезно переработать структуру БД
2) Перефразируем ваш запрос:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND pc.characteristic_id= ANY   (2,     19,        20)
        AND pc.selected_option_id=ANY (129,   2001569,   2001570, 2001571)
     )

Или, "совсем по русски"

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND (pc.characteristic_id =2 OR  pc.characteristic_id =19 or pc.characteristic_id =20)
        AND (pc.selected_option=129 OR pc.selected_option=2001569 OR pc.selected_option=2001570 or pc.selected_option=2001571)
     )

То есть вместо пересечения характеристик, вы выбираете их объединение
Ну и, поскольку подзапрос вообще говоря самостоятелен, и независим от внешнего запроса, я не очень понимаю, что у вас в нем делает p.id и как оно вообще будет работать.
3) Если попробовать написать то, что вы хотели, получится примерно следующее

SELECT *
FROM products p
WHERE p.id IN
     (SELECT pc.product_id
      FROM product_characteristic pc,  product_characteristic pc1,product_characteristic pc2,  
      WHERE pc.product_id=pc1.product_id and pc.product_id=pc2.product_id
        AND pc.characteristic=2 AND pc.selected_option_id=129
        AND pc1.characteristic=19  AND pc1.selected_option_id=2001569
        AND pc2.characteristic=20  AND pc2.selected_option_id in  (2001570, 2001571)
     )

Во-первых - убираем DISTINCT из подзапроса - он даст нам перечень product_id, и даже если они будут повторяться, основной запрос выберет только уникальные строки

Во вторых - добавляем еще два псевдонима к таблице product_characteristic
для каждого псевдонима ставим свое условие по id характеристики и id выбранной опции

Ну и сообщаем, что искомый product_id для всех таблиц product_characteristic у вас одинаковый.

Если у вас selected_option_id уникален для каждой опции, можно исключить из запроса поле selected_option_id
тогда получится примерно так:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT pc.product_id
      FROM product_characteristic pc,  product_characteristic pc1,product_characteristic pc2,  
      WHERE pc.product_id=pc1.product_id and  pc.product_id=pc2.product_id
        AND pc.selected_option_id=129
        AND pc1.selected_option_id=2001569
        AND pc2.selected_option_id in  (2001570, 2001571)
     )

Comments

[Valeriu Vodnicear]

Ну и, поскольку подзапрос вообще говоря самостоятелен, и независим от внешнего запроса, я не очень понимаю, что у вас в нем делает p.id и как оно вообще будет работать.

на самом деле я показал только эту часть запроса потому что только в ней есть проблема

сам запрос очень большой, если бы показал его то никому наверное не хотелось бы разбираться как он работает

[Valeriu Vodnicear]

Если у вас selected_option_id уникален для каждой опции, можно исключить из запроса поле selected_option_id
тогда получится примерно так:

да, selected_option_id уникален для каждой опций

[Valeriu Vodnicear]

всё работает кроме:
AND pc2.selected_option_id in (2001570, 2001571)

[hell]

очень странно.
а если опять "по русски" попробовать:

AND (pc2.selected_option_id=2001570 OR  pc2.selected_option_id=2001571)

и что говорит explain analyze?

[hell]

Valeriu Vodnicear, Но вообще говоря, не работать он не может. пришлите запрос целиком - возможно где-то в другой части у вас ошибка