Выбор по n условиям с AND из одной колонки

Question

[be3_max]

Здратсвуйте!
Подскажите пожалуйста оптимальный вариант поиска по таблице в одной колонке, по n параметрам с условием AND.
Например, есть таблица:

productID___variantID___optionID
_711__________17_________7__
_712__________18_________7__
_700__________17_________7__
_701__________15_________7__
_713__________17_________7__
_708__________17_________7__
_699__________17_________6__
_699__________17_________7__


И нужно получить productID где variantID=17 и optionID=6 и optionID=7.

То есть как бы:

SELECT productID FROM tablname WHERE variantID=17 AND optionID=6 AND optionID=7;

но такой запрос понятно ничего не вернет, т.к. optionID=6 AND optionID=7 в одной строке быть не может. Спасибо!

UPD: уточнил вопрос

Comments

[agul]

Вам надо получить два различных ProductID?

[be3_max]

Нужно получить один ProductID, у которого optionID=6 и optionID=7.

[Anei]

Возможен ли в таблице такой набор данных?

_699__________17_________6__
_699__________17_________7__
_699__________18_________7__

Конечно, хотелось бы увидеть первичный ключ для таблицы и, если есть, уникальные ключи.

[be3_max]

Да, да возможен.

Answer 1

[Иван]

SELECT t1.productID 
FROM tablename t1, tablename t2 
WHERE t1.optionID=6 AND t2.optionID=7 AND t1.productID=t2.productID;

Comments

[Иван]

Естественно, возникнут проблемы при попытке увеличить количество опций ))

[be3_max]

Спасибо, пока наиболее подходящий вариант.

Answer 2

[Александр Кит]

Еще такой может быть вариант:

select p.productId from Products p where p.orderId = 6 and
exists(select productId from Products where productId = p.productId and orderId=7)

Comments

[Александр Кит]

А предыдущий вариант лучше через inner join:

select p.productId from Products p1
inner join Products p2 on p1.productId = p2.productId and p2.orderId = 7
where p1.orderId = 6


Answer 3

[Anei]

SELECT productID 
FROM 
    (SELECT productID, count(productID) AS cnt 
     FROM 
        (SELECT DISTINCT productID, optionID FROM tablname) as a1
    WHERE optionID IN (6,7)
    GROUP BY user_id) AS a2 
WHERE cnt=2

Страшно? Мне тоже. Но это проще, чем вариант dohlik и проще масштабируется.

В любом случае, запрос очень тяжелый. А для чего планируется использовать такую выборку? Может, есть есть способ обойтись без такой тяжелой выборки?

Comments

[be3_max]

Спасибо, за вариант. Планируется использовать выборку для фильтра каталога программ, по нескольким параметрам одной характеристики программы.

[Anei]

Немного туплю. Эквивалентный вариант, но проще:

SELECT productID, count(productID) AS cnt 
FROM (SELECT DISTINCT productID, optionID FROM tablename) as a1
WHERE optionID IN (6,7)
GROUP BY productID HAVING cnt=2

Есть ощущение, что на больших выборках при наличии индекса по полю optionID вариант с EXISTS будет работать быстрее.

[Anei]

В случае, если на полях productID и optionID висит уникальный индекс, вложеный запрос исчезает:

SELECT productID, count(productID) AS cnt 
FROM tablename
WHERE optionID IN (6,7)
GROUP BY productID HAVING cnt=2

Answer 4

[xy4]

variantID у тебя 17 везде, а чтобы выбрать productID c optionID 6 и 7 используй «OR:

SELECT productID FROM tablname WHERE variantID=6 OR variantID=7;

Comments

[xy4]

Конечно же

SELECT productID FROM tablname WHERE productID=6 OR productID=7;

[be3_max]

Извиняюсь, перепуталoptionID=6 AND optionID=7 поправил вопрос.
если:

SELECT productID FROM tablname WHERE optionID=6 OR optionID=7;

то мне вернется та же таблица, а нужен productID у которого optionID=6 и optionID=7

[xy4]

Понял вопрос, тогда ответ 'а должен подойти.

Answer 5

[WarSib]

Попробую сравнить два варианта:

Anei предложил хороший, красивый, легко масштабируемый запрос.
Но, в вопросе звучало так «Выбор по n условиям с AND»
допустим N = 10
тогда WHERE optionID IN (6,7,1,,,,23,,,)
HAVING cnt=10

Выглядит красиво, но не очень очевидно как оптимизатор сможет использовать индексы.
А групповая операция с хевингом очень медленная.

Поскольку речь видимо идет о расширенном поиске (которому не помогает кеширование) можно предположить что этот запрос будет чувствителен к быстродействию. И при определенных условиях вообще может класть сайт.

Таким образом я бы рассмотрел вариант который предложил tenbits (с иннер джойн)
для масштабирования там всего лишь надо добавлять еще один иннер джоин на каждый параметр.
Для оптимизации надо построить составной индекс optionID, productID (именно в таком порядке)
тогда каждый джоин будет делать выборку исключительно из индекса (не будет обращений к данным самой таблицы), причем крупноблочно по параметру.
в выборке продукты будут отсортированы по productID
т.е. заджоинить такие выборки очень просто.
Такой вариант более прогнозируемый в плане «плана запроса» при большом N

Но реальный результат будет ооочень сильно зависть от реальных данных. Надо экспериментировать.
Вполне возможно что разница не будет замечена вообще.