Good day! Help to make a request to the database mysql. Given table:

|id|haracterictic_id| value |modification_id| --------------------------------------------- |1 | 40 | Седан | 122 | |2 | 40 | Пикап | 123 | |3 | 39 |Автомат| 122 |

You need to make a request so that it haracterictic_id for the fields haracterictic_id and modification_id and haracterictic_id exact value, for example:

you need to find the value of the modification_id field, if you know that haracterictic_id will be '40' and '39', and value will be 'Sedan' and 'Automatic', respectively

  • ...WHERE modification_id IN (39, 40) AND value IN ('Седан', 'Автомат') ? The condition field IN (val1, val2...) can be described as (field = val1 or field = val2) , then your condition takes the form ...WHERE (modification_id = 39 OR modification_id = 40) AND (value = 'Седан' OR value = 'Автомат') - BOPOH
  • I need an exact match for all values, not "either this or that" - wstudiokiwi

2 answers 2

If I correctly understood your goal, then something like:

 select modification_id from tableX where (haracterictic_id, value) in( ('40','Седан'), ('39','Автомат') ) group by modification_id having count(1)=2

The value of count (1) in having must be equal to the number of conditions to be checked, which would select those records that have coincidence of all conditions.

  • Thanks It works. And if you complicate the task and add another modification table through the left join and also do a search for it - wstudiokiwi
  • I understand that the query is built correctly? sqlfiddle.com/#!9/e9bf9/2 - wstudiokiwi
  • @wstudiokiwi We must look at the specific situation. But if you add another table directly to this request for the left, then the number of records will not change if there are 0 or 1 records in that table. so perhaps it will not be necessary to left, but just join, which would only work if a record is found in the second table or to enter from the other side ... - Mike
  • @wstudiokiwi Looked at your fiddle. Well, in general, you can, only the word left is not needed. - Mike
  • @wstudiokiwi I would actually write something like sqlfiddle.com/#!9/e9bf9/12, in principle, absolutely the same thing and the optimizer will most likely perform the query in exactly the same way. Personally, it just seems more logical to me looking :) - Mike

Here is an example solution

IN can be used for multiple objects.

Tables

 create table tt (`id` int,`haracterictic_id` int,`value` varchar(100),`modification_id` int ); INSERT INTO tt (`id` ,`haracterictic_id` ,`value` ,`modification_id` ) VALUES (1, 40,'Седан',122), (2, 40,'Пикап',123), (3, 39,'Автомат',122) ;

Request

 select * from tt where ( haracterictic_id,value ) in ((40,'Седан'), (39,'Автомат'))

fiddle

  • In this version of sqlfiddle.com/#!9/d9b69/5, it is also found, then 1 record, but it shouldn’t find anything at all, because no exact match - wstudiokiwi
  • Yes, I already understood that I didn’t understand the question correctly (I thought that I just needed to find all the modifications that match at least one pair of haracterictic_id,value ) - Batanichek