I have a table like this http://sqlfiddle.com/#!9/052381/1

I need to create a request that will find VIN codes that meet the following conditions:

1. VIN starts with XTA%
2. I have registration history: date_reg_last values: 1306440000,1506715200,1555963200. You need to select only those VIN codes that have exactly these values. If there are more or less records - VIN does not match
3. I have an owner_type that matches the values ​​1306440000,1506715200,1555963200: 2, 2, 2. Ie. for record 1306440000 owner_type must be 2, for record 1506715200 also 2, etc. The type can be different for each entry.
4. Similarly to the third point, I have regions: УЛЬЯНОВСК Г.,УЛЬЯНОВСК Г.,С РУНГА
5. I have a year, it should be in all records.

I tried making a request like this

SELECT * 
FROM `ac_gibdd_shortinfo` 
WHERE `vin` LIKE 'XTA%' 
  AND `model` LIKE '19%' 
  AND `date_reg_first` IN (0,1506715200,1555963200) 
  AND `date_reg_last` IN (1306440000,1506715200,1555963200) 
  AND `year` LIKE '2011' 
  AND `location` IN ('УЛЬЯНОВСК Г.','С РУНГА')

But it finds records that have a different number of registration records. There is only one thought: get all the matching records and then filter them by number with an additional request.

CodePudding user response：

Test this:

SELECT * 
FROM `ac_gibdd_shortinfo` t0
WHERE `vin` LIKE 'XTA%' 
  AND `model` LIKE '19%' 
  AND `date_reg_first` IN (0,1506715200,1555963200) 
  AND `date_reg_last` IN (1306440000,1506715200,1555963200) 
  AND `year` LIKE '2011' 
  AND `location` IN ('УЛЬЯНОВСК Г.','С РУНГА')
  AND NOT EXISTS ( SELECT NULL
                   FROM ac_gibdd_shortinfo t1
                   WHERE t0.vin = t1.vin
                     AND t1.date_reg_first NOT IN (0,1506715200,1555963200) )
  AND NOT EXISTS ( SELECT NULL
                   FROM ac_gibdd_shortinfo t2
                   WHERE t0.vin = t2.vin
                     AND t2.date_reg_last NOT IN (1306440000,1506715200,1555963200) )
  AND NOT EXISTS ( SELECT NULL
                   FROM ac_gibdd_shortinfo t3
                   WHERE t0.vin = t3.vin
                     AND t3.location NOT IN ('УЛЬЯНОВСК Г.','С РУНГА') )

PS. According indices will improve.

and have count (1306440000,1506715200,1555963200) - 3 records in total by VIN – blood73

SELECT vin, model, date_reg_first, date_reg_last, `year`, location 
FROM `ac_gibdd_shortinfo` t0
WHERE `vin` LIKE 'XTA%' 
  AND `model` LIKE '19%' 
  AND `date_reg_first` IN (0,1506715200,1555963200) 
  AND `date_reg_last` IN (1306440000,1506715200,1555963200) 
  AND `year` LIKE '2011' 
  AND `location` IN ('УЛЬЯНОВСК Г.','С РУНГА')
  AND 3 = ( SELECT COUNT(*)
            FROM ac_gibdd_shortinfo t1
            WHERE t0.vin = t1.vin );