Filter from 2 tables and join 2 other tables

Question

I have a Postgres database with the following table layout:

Let's assume the contents are as follows:

cars                                            properties
| id  |  name    |  desc     |                  |  id  |  name    |  type  |
------------------------------                  ----------------------------
|  1  | 'Toyota' | 'Japanese'|                  |  1   | 'length' |  'dim' |
------------------------------                  ----------------------------
|  2  | 'Ford'   | 'American'|                  |  2   | 'vin'    |  'reg' |
------------------------------                  ----------------------------

float_properties                                string_properties
| car_id | property_id |  min  |  max  |        | car_id | property_id |  value  |
----------------------------------------        ----------------------------------
|   1    |      1      | 10.5  |  12.5 |        |    1   |      2      |  'abc'  |
----------------------------------------        ----------------------------------
|   2    |      1      |  9.5  |  14   |        |    2   |      2      |  'def'  |
----------------------------------------        ----------------------------------

How do I write a query to get the car name given values from both float and string properties tables:

For ex: Give car name and description for 'length' min > 10, max < 13 and vin = 'abc'(I understand the VIN is unique for each car, but let's ignore that for now).

I tried

SELECT
      C.name,
      C.description,
      P.name,
      P.type
FROM 
      properties P
INNER JOIN float_properties FP 
    ON FP.property_id = P.id
INNER JOIN string_properties SP 
    ON SP.property_id = P.id
INNER JOIN cars C 
    ON C.id = FP.car_id AND C.id = SP.car_id
WHERE
    P.name = 'length' AND FP.min > 10 AND FP.max < 13
    AND
    P.name = 'vin' AND SP.value = 'abc';

but this returns zero rows. What's the correct query here?

EDIT: After reading @ginkul's comment it made me think of 2 use cases - my original question is for use case 1 below:

1. Find a car(s) that must satisfy conditions from both tables: For example:

   Condition 1: Car that has a property named 'length' and their limits min > 10 AND FP.max < 13

   Condition 2: Car that has a property named 'vin' with value 'abc'

2. Find cars that need to satisfy only 1 of the conditions; in other words, all cars with 10 < 'length' < 13 union all cars with property 'vin' = 'abc'.

Answer 1

Basically, you need to JOIN to properties twice, once for the string table and once for the float table:

SELECT C.name, C.description, PF.name, PF.type, PS.name, PS.type
FROM Cars C JOIN
     float_properties FP 
     ON C.id = FP.car_id JOIN
     properties PF
     ON FP.property_id = PF.id JOIN
     string_properties SP 
     ON c.id = SP.car_id JOIN
     properties PS
     ON SP.property_id = PS.id 
WHERE (PF.name = 'length' AND FP.min > 10 AND FP.max < 13) AND
      (PS.name = 'vin' AND SP.value = 'abc');

Answer 2

You start from inside outwards - first you find the ID of the property, then you search for the needed values of that property, then you find the cars which have such values:

SELECT name, description
FROM cars
WHERE id IN
  (SELECT car_id 
  FROM string_properties AS sp
  WHERE car_id = 
    (SELECT car_id 
    FROM float_properties AS fp 
    WHERE property_id = 
      (SELECT id AS propID 
        FROM properties AS p1
        WHERE p1.name = 'length'
      )
      AND fp.min > 10
      AND fp.max < 13
    )
    AND property_id = (SELECT id AS propID FROM properties AS p2 WHERE p2.name = 'vin')
    AND sp.value = 'abc'
  )
ORDER BY name, description

For case 2 you will slightly modify the same query:

SELECT name, description
FROM cars
WHERE id IN
  (SELECT car_id 
  FROM string_properties AS sp
  WHERE property_id = 
    (SELECT id AS propID 
      FROM properties AS p2 
      WHERE p2.name = 'vin'
    )
    AND sp.value = 'abc'
  UNION ALL
  SELECT car_id 
  FROM float_properties AS fp 
  WHERE property_id = 
    (SELECT id AS propID 
      FROM properties AS p1
      WHERE p1.name = 'length'
    )
    AND fp.min > 10
    AND fp.max < 13
  )
)
ORDER BY name, description

Answer 3

You can't inner-join from both detail tables. Try this.

SELECT
      C.name,
      C.description,
      coalesce(Pfp.name, Psp.name) pname,
      coalesce(Pfp.type, psp.type) ptype
FROM cars C 
LEFT JOIN float_properties FP 
    ON FP.car_id = c.id
LEFT JOIN properties Pfp
    ON pfp.id = fp.property_id
LEFT JOIN string_properties SP 
    ON SP.car_id = c.id
LEFT JOIN properties Psp
    ON psp.id = sp.property_id
WHERE
    Pfp.name = 'length' AND FP.min > 10 AND FP.max < 13
    OR
    Psp.name = 'vin' AND SP.value = 'abc';

The above query hasn't been tested.

Take into account that in your current design there might be multiple rows returned. You can't ask for a single property description and filter for two properties, all at the same time. If you want then you can use something like string_agg() to aggregate all property descriptions and thus have one result row per car.