Блог

У меня есть четыре таблицы, подобные следующим :

 Cars:
car_id | make_id
1      | 1
2      | 3

Cars Makes 
make_id  | make_name
1        | BMW
2        | Ferrari
3        | Mercedes 


Car Properties 
car_id  | property_id | property_value
1       | 1           | Automatic
1       | 2           | 1000
1       | 3           | Diesel
2       | 1           | Manual
2       | 2           | 15000
2       | 3           | Gasoline

Properties
property_id | property_name
1           | Transmission
2           | Mileage
3           | Fuel
  

Как вы можете видеть, у каждого car есть make_id из таблицы «Makes» .

И там есть отдельная таблица, содержащая все основные свойства автомобилей. Затем есть таблица «Свойства автомобиля», которая содержит car_id, property_id, property_value

Теперь я хочу сделать следующий запрос: получить автомобили BMW с автоматической коробкой передач и пробегом 1000 на дизельном топливе. давайте предположим, что форма может предоставить следующее: make_id = 1 (BMW) свойства = Автоматический, 1000, дизельный

P.S: Все будет в порядке, если я просто получу результат car_id

Предполагая, что запрос должен удовлетворять всем трем свойствам:

 SELECT c.car_id
FROM
    Cars c INNER JOIN (
        SELECT car_id, COUNT(*) AS prop_count
        FROM
            CarProperties
        WHERE
            (property_id = 1 AND property_value = 'Automatic')
            OR (property_id = 2 AND property_value = '1000')
            OR (property_id = 3 AND property_value = 'Diesel')
        GROUP BY car_id
    ) AS cp ON c.car_id = cp.car_id AND cp.prop_count = 3
WHERE
    c.make_id = 1;
  

Затем это пришло мне в голову:

 SELECT c.car_id
FROM
    Cars c INNER JOIN (
        SELECT car_id FROM CarProperties
        WHERE property_id = 1 AND property_value = 'Automatic'
    ) AS t ON c.car_id = t.car_id INNER JOIN (
        SELECT car_id FROM CarProperties
        WHERE property_id = 2 AND property_value = '1000'
    ) AS m ON c.car_id = m.car_id INNER JOIN (
        SELECT car_id FROM CarProperties
        WHERE property_id = 3 AND property_value = 'Diesel'
    ) AS f ON c.car_id = f.car_id
WHERE
   c.make_id = 1;
  

Не на 100% зависит от синтаксиса mysql (извините, что слишком много живу в TSQL), но это идея взаимосвязи для использования.

 FROM Car 
JOIN CarProperties Trans
    ON Car.car_id = Trans.CarID AND Trans.property_id = 1
JOIN CarProperties Mileage 
    ON Car.car_id = Mileage.CarID AND Mileage.property_id = 2
JOIN CarProperties Fuel 
    ON Car.car_id = Fuel.CarID AND Fuel.property_id = 3
  

Ваш выбор может основываться на пробеге, топливе или переходе, как и ваше предложение where

Поскольку входные данные содержат только такие значения, как «Automatic, 1000, Diesel», и ни одного значения, подобного «Трансмиссия, пробег, топливо», вам придется игнорировать таблицу свойств и молиться, чтобы ваши типы свойств никогда не включали перекрывающиеся ключи (или более одного числового типа). Кроме того, поскольку входные данные уже содержат make_id напрямую, мы также можем не использовать Cars Makes таблицу.

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

 SELECT c.car_id 
FROM cars c
INNER JOIN `Car Properties` cp1 
    ON cp1.car_id = c.car_id AND cp1.property_value = 'Automatic'
INNER JOIN `Car Properties` cp2 
    ON cp2.car_id = c.car_id AND cp2.property_value = 'Diesel'
INNER JOIN `Car Properties` cp3 
    ON cp3.car_id = c.car_id  AND cp3.property_value = '1000'
  

вы можете сначала объединить свойства Car и Properties, присвоить им псевдоним и получить массив car_id, затем выполнить поиск Cars объединение Cars производится «В» (car_id_array).

Возможно, это могло бы сработать:

 SELECT car_id
FROM Cars LEFT JOIN CarMakes USING (make_id)
     JOIN (SELECT car_id
           FROM CarProperties JOIN Properties USING (property_id)
           WHERE property_name='Transmission' AND property_value='Automatic') a
     JOIN (SELECT car_id
           FROM CarProperties JOIN Properties USING (property_id)
           WHERE property_name='Mileage' AND property_value='1000') b
     JOIN (SELECT car_id
           FROM CarProperties JOIN Properties USING (property_id)
           WHERE property_name='Fuel' AND property_value='Diesel') c
WHERE make_name = 'BMW'
  

 select cars.car_id
   from
      cars 
         join `car properties` cp
            on cars.car_id = cp.car_id
            join properties p
               on cp.property_id = p.property_id
   where
         cars.make_id = 1
      and (   ( p.property_name = "Transmission" and cp.property_Value = "Automatic" )
           OR ( p.property_name = "Mileage" and cp.property_Value = "1000" )
           OR ( p.property_name = "Fuel" and cp.property_Value = "Diesel" )
          )
   group by
      cars.car_id
   having
      count(*) = 3