1

Topic: How to make sampling under a cycle (each subsequent request - to result previous)

Good afternoon!
I want to make one request the following:

SELECT * FROM ` properties ` WHERE ` field_1 ` = ' some_value ' AND ` field_2 ` = ' some_other_value ';
Cycle {
SELECT * FROM [result previous SELECT] WHERE ` field_1 ` = ' some_value ' AND ` field_2 ` = ' some_other_value ';
}

Actually, the task in what:
Properties of the goods are stored in the table... The table Structure is that: id|prod_id|feature_id|value
Communications:
prod_id => id from the table products
feature_id => id from the table features
I need to receive the goods at which necessary to me feature_id and value and thus filters can be a little...

2

Re: How to make sampling under a cycle (each subsequent request - to result previous)

atillus wrote:

filters can be a little...

Depending on logic filters unite operators OR or AND. In one request, without .

3

Re: How to make sampling under a cycle (each subsequent request - to result previous)

Akina, it is impossible so...
That is why:
id, prod_id, feature_id, value
1,234,3, green
2,237,3, red
3,245,4, big
3,246,4, small
-- If so
WHERE (feature_id = 3 AND value = ' green ') AND (feature_id = 4 AND value = ' big ')
-- Result: a zero
-- If so
WHERE (feature_id = 3 AND value = ' green ') OR (feature_id = 4 AND value = ' big ')
-- Result: all

4

Re: How to make sampling under a cycle (each subsequent request - to result previous)

I made for the sample a small example (facilitated and simplified) -  put.
If to do so:

SELECT DISTINCT products.id, products.prod_name FROM products
LEFT JOIN properties ON properties.prod_id = products.id
WHERE properties.feature_id = 1 AND properties.value = ' black'
OR properties.feature_id = 2 AND properties.value = ' 41'

That we receive also all black, and all 41st size
And if so:

SELECT DISTINCT products.id, products.prod_name FROM products
LEFT JOIN properties ON properties.prod_id = products.id
WHERE properties.feature_id = 1 AND properties.value = ' black'
AND properties.feature_id = 2 AND properties.value = ' 41'

That we do not receive anything...
Therefore I search for possibility in turn to apply conditions to the previous result...

5

Re: How to make sampling under a cycle (each subsequent request - to result previous)

It is necessary to select all records with the necessary combinations (feature_id, value) to group in a product and to count an amount of the records selected for a product.

SELECT products.id, products.prod_name
FROM products
INNER JOIN properties ON properties.prod_id = products.id
-- It is possible and so
-- WHERE (properties.feature_id = 1 AND properties.value = ' black ')
-- (OR properties.feature_id = 2 AND properties.value = ' 41 ')
-- But is more evident so
WHERE (properties.feature_id, properties.value) IN ((1, ' black '), (2, ' 41 '))
GROUP BY products.id
-- If Server Mode = ONLY_FULL_GROUP_BY, also
-- , products.prod_name
HAVING COUNT (- if duplication of records is possible
-- DISTINCT
products.id) = 2

6

Re: How to make sampling under a cycle (each subsequent request - to result previous)

Akina, thanks. That is necessary! GROUP BY and then HAVING COUNT - the right decision.