4 таблицы SQL Присоединение Проблема

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

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

Как видите, у каждой машины есть make_id из таблицы "Makes" .

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

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

PS: все будет хорошо, если я просто получу результат car_id

Источник

6 ответов

Решение

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

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

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

Источник

Поскольку вход имеет только такие значения, как "Автоматически,1000, Дизель" и ни одно из них, например "Трансмиссия, Пробег, Топливо", вам придется игнорировать таблицу свойств и молиться о том, чтобы ваши типы свойств никогда не включали перекрывающиеся ключи (или более одного числового типа).). Кроме того, поскольку вход уже имеет 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'
Источник

Возможно, это может сработать:

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'
Источник

Сначала вы можете присоединиться к свойствам и свойствам автомобилей, присвоить им псевдоним и получить массив car_id, а затем выполнить поиск "Присоединение к автомобилям". "Делает IN" (car_id_array).

Источник 
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
Источник
Другие вопросы по тегам