Select all rows with multiple possible values

how to select all rows which have same value in some column
sql select rows with different values in one column
sql query to select multiple rows with same values
sql group rows with different value
sql group rows with same value
sql not equal to multiple values
sql count
sql query for multiple values in single field

I am trying to get all fields from the product table (*) that have the following set of sub_property :

subprop_name=X subprop_value=Y

I have the following tables :

https://imgur.com/a/y4LGqMI (couldn't upload the picture because the format was not accepted)

So, for an exemple, if I have two products wich has in their sub_property table a entry like this : subprop_name=X subprop_value=Y

I would like to return it. As described by the schema, a product can have multiple sub_property entries !

So far, this is what I have :

SELECT prod_id,prod_name from product WHERE product.prod_id IN
(
    SELECT property.product_prod_id FROM property WHERE property.prop_id IN
    (
        SELECT property_prop_id from sub_property WHERE
        (
            sub_property.subprop_name="Type de scanner" AND sub_property.subprop_value="par transparence"
        )
        OR
        (
            sub_property.subprop_name="Pages/minute maximum" AND subprop_value="8.5 pages"
        )
    )
)

But obviously, it doesn't work because of the 'OR'.

It returns me all items that have one of the set of sub_property instead of all the products that have all the sets of sub_property.

DATABASE HERE

Using JOIN's and an IN for the tupples could be a simple solution for this.

SELECT p.prod_id, p.prod_name
FROM product p
JOIN property AS prop 
  ON prop.product_prod_id = p.prod_id
JOIN sub_property AS subprop 
  ON subprop.property_prop_id = prop.prop_id
WHERE (subprop.subprop_name, subprop.subprop_value) IN (
  ('Type de scanner', 'par transparence'), 
  ('Pages/minute maximum', '8.5 pages')
)                          
GROUP BY p.prod_id, p.prod_name

Select only those records that have different/multiple values for a , You could do something like: select distinct x.id, x.lastname, x.firstname, x.email from t as x join ( select id from t group by id having  Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

So... I am not sure if it's the correct way to validate two answers, but I got 2 working answers.

This is the first one from @Steff Mächtel using LEFT JOIN (https://stackoverflow.com/a/53915792/5454875)

And the second one is from @Shidersz (see comments below the original question), using INNER JOIN)

"I used and approach using INNER JOIN, is something like this what you need? db-fiddle.com/f/t6RrnhDPQuEamjf2bTxFeX/5"

EDIT

@Shidersz solution doesn't work because it selects all products who as at least one of the condition, wich I don't want. I want the product to have all the conditions.

SQL Server SELECT DISTINCT, In other words, the DISTINCT clause treats all NULL “values” as the same value. SQL Server SELECT DISTINCT - multiple columns example before Both DISTINCT and GROUP BY clause reduces the number of returned rows in the result  Is it possible to specify a regex here for the value that is checked. Suppose the value in a row for a particular column in the table is 'hello world foo bar' and I need to return this row if the string 'foo' is present in the column. – Aditya Nov 29 '16 at 7:10

UPDATE 2:

I would suggest to use LEFT JOIN for each property and each sub_property and then check the value inside WHERE condition:

SELECT product.prod_id, product.prod_name
FROM product 
LEFT JOIN property AS property_a ON property_a.product_prod_id = product.prod_id AND
                                    property_a.prop_name = "PROPERTY_GROUP_3"
LEFT JOIN sub_property AS sub_property_a ON sub_property_a.property_prop_id = property_a.prop_id AND 
                                            sub_property_a.subprop_name="property_4"
LEFT JOIN property AS property_b ON property_b.product_prod_id = product.prod_id AND
                                    property_b.prop_name = "PROPERTY_GROUP_4"
LEFT JOIN sub_property AS sub_property_b ON sub_property_b.property_prop_id = property_b.prop_id AND 
                                            sub_property_b.subprop_name="property_3"
WHERE sub_property_a.subprop_value="value_of_property_4" AND 
      sub_property_b.subprop_value="value_of_property_3"
GROUP BY product.prod_id

Example: https://www.db-fiddle.com/f/wk344Gt6hm98xEhM4jei92/6

Example with 2 new "KEYS" (Index) for better performance:

ALTER TABLE `property`
 ... ADD KEY `prop_name` (`prop_name`);

ALTER TABLE `sub_property`
 ... ADD KEY `subprop_name` (`subprop_name`);

https://www.db-fiddle.com/f/wk344Gt6hm98xEhM4jei92/7

Example with INNER JOIN instead of LEFT JOIN

I see no difference with EXPLAIN on test data, maybe Mysql optimizer handles this internal equal

SELECT product.prod_id, product.prod_name
FROM product 
INNER JOIN property AS property_a ON property_a.product_prod_id = product.prod_id AND
                                     property_a.prop_name = "PROPERTY_GROUP_3"
INNER JOIN sub_property AS sub_property_a ON sub_property_a.property_prop_id = property_a.prop_id AND 
                                             sub_property_a.subprop_name="property_4" AND
                                             sub_property_a.subprop_value="value_of_property_4"
INNER JOIN property AS property_b ON property_b.product_prod_id = product.prod_id AND
                                     property_b.prop_name = "PROPERTY_GROUP_4"
INNER JOIN sub_property AS sub_property_b ON sub_property_b.property_prop_id = property_b.prop_id AND 
                                             sub_property_b.subprop_name="property_3" AND
                                             sub_property_b.subprop_value="value_of_property_3"
GROUP BY product.prod_id

https://www.db-fiddle.com/f/wk344Gt6hm98xEhM4jei92/8

SQL joins and how to use them, With this virtual join table created, the SELECT column_list FROM part of our The value in the id column of the users table for the user Jane Smith is 5 A LEFT JOIN or a LEFT OUTER JOIN takes all the rows from one table, Multiple Joins. It is possible, and indeed common, to join more than just two tables together. Extract all rows from a range that meet criteria in one column [Filter] The image above shows filtered records based on two conditions, values in column D are larger or equal to 4 or smaller or equal to 6. Here is how to apply Filter arrows to a dataset. Select any cell within the dataset range. Go to tab "Data" on the ribbon. Click "Filter button".

IN Condition, It tests a value for membership in a list of values or subquery If you use the lower form of this condition (with multiple expressions to the left of the operator), SELECT * FROM employees WHERE salary NOT IN (SELECT salary FROM to null, then all rows evaluate to FALSE or UNKNOWN , and no rows are returned​. Under the first name, select a number of empty cells that is equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter to complete it (in this case, you will be able to edit the formula only in the entire range where it's entered).

How can I subset a data set?, The R program (as a text file) for all the code on this page. in R. This page aims to give a fairly exhaustive list of the ways in which it is possible to subset a data set in R. Subsetting rows using the subset function Subsetting rows using multiple conditional statements Subsetting rows selecting on more than one value. If you want to solve this problem with fewer steps, you can use Kutools for Excel’s Select Specific Cells function to select the rows that meet the criteria, and then copy them to another location. 1. Select the column you extract rows based on, and click Kutools > Select > Select Specific Cells. See screenshot: 2.

Documentation: 10: SELECT, AS ( select | values | insert | update | delete ) TABLE [ ONLY ] table_name [ * ] SELECT ALL (the default) will return all candidate rows, including duplicates. When there are multiple queries in the WITH clause, RECURSIVE should be written In these cases it is not possible to specify new names with AS ; the output  List all matched instances of a value with array formula. With the following array formula, you can easily list all match instances of a value in a certain table in Excel. Please do as follows. 1. Select a blank cell to output the first matched instance, enter the below formula into it, and then press the Ctrl + Shift + Enter keys simultaneously.

Comments
  • Why not Just use select * from tbl where colA = 'valX' or colA = 'valY'
  • Because I need the products, that have all the sets of sub_properties.
  • Can you provide example data? Maybe on db-fiddle.com? Its hard to test without having data :(
  • Yes, I understand, but it's quiet a large database. Shell I create an account for you to test on it?
  • You have to update your fiddle and post the new (updated) link...
  • The question, do you need "OR" for the properties or an "AND" solution? :D @Shidersz solution uses "OR" in WHERE condition. Which means you get all products if one of the conditions matches. Is that what you want? Or you want only products which both conditions matches? Then you can use my solution.
  • Woupsi ! I just found out that your solution works, but due to multiplies JOIN's, I can't really use your solution because it takes forever to load an item. And the time between results increase quiet rapidly. Do you know how you can make your query faster ?
  • I have updated my answer a little bit (Left joins to property have an additional AND property_a.prop_name = "PROPERTY_GROUP_3" condition). You can use "EXPLAIN" to find the bottlenecks. Here is an example output of explain: db-fiddle.com/f/wk344Gt6hm98xEhM4jei92/5 You can see possible keys and non of them can be used. You can add additional index for your fields "subprob_name", "prob_name", etc. and check with "EXPLAIN" if this keys are used. dev.mysql.com/doc/refman/8.0/en/using-explain.html
  • Sadly, 0 rows returned :( !
  • Thx for example data :) i have updated my answer. Maybe this solution works for you.
  • Thank you so much ! Also, Merry Christmas !