MySql | Faceted search changed

Question

Maybe you will help me with my sql query. My question is based on another question it's here: Mysql | Faceted search Everything is the same and I need the same result but the tables are little bit different. I can't construct my query. Please take a look at this sql fiddle:

My table structure:

CREATE TABLE products
    (`id` int, `description` varchar(9), `user_id` int);

INSERT INTO products
    (`id`, `description`, `user_id`)
VALUES
    (1, 'my car', 3),
    (2, 'dream car', 3),
    (3, 'New car', 3),
    (4, 'Old car', 4);

CREATE TABLE fields
    (`id` int, `field_name` varchar(14)); /*meta_name*/

INSERT INTO fields
    (`id`, `field_name`)
VALUES
    (1, 'Make'),
    (2, 'Model'),
    (3, 'Color'),
    (4, 'Car Type'),
    (5, 'Interior Color');

CREATE TABLE fields_values
    (`id` int, `field_id` int, `field_value` varchar(7)); /*meta_value*/

INSERT INTO fields_values
    (`id`, `field_id`, `field_value`)
VALUES
    (1, 1, 'BMW'),
    (2, 2, '3Series'),
    (3, 3, 'White'),
    (4, 4, 'Coupe'),
    (5, 5, 'Black'),
    (6, 1, 'BMW'),
    (7, 2, '2Series'),
    (8, 3, 'Black'),
    (9, 4, 'Coupe'),
    (10, 5, 'Grey'),
    (11, 1, 'Honda'),
    (12, 2, 'Civic'),
    (13, 3, 'Red'),
    (14, 4, 'Sedan'),
    (15, 5, 'Black');

CREATE TABLE products2fields_values
    (`id` int, `product_id` int, `field_value_id` int);
INSERT INTO products2fields_values
    (`id`, `product_id`, `field_value_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4),
    (5, 1, 5),
    (6, 2, 1),
    (7, 2, 2),
    (8, 2, 3),
    (9, 2, 4),
    (10, 2, 5),
    (11, 3, 1),
    (12, 3, 2),
    (13, 3, 3),
    (14, 3, 4),
    (15, 3, 5); 

And my wrong query:

SELECT field_name, field_value, COUNT(DISTINCT pid) count
FROM fields ft 
JOIN fields_values fvt
    ON fvt.field_id = ft.id

JOIN products2fields_values p2fv
    ON p2fv.field_value_id = fvt.id

LEFT JOIN (
    SELECT p.id pid
    FROM products p   
    JOIN products2fields_values p2fv
        ON p2fv.product_id = p.id
    JOIN fields_values fvt
        ON fvt.id = p2fv.field_value_id
    JOIN fields ft
        ON ft.id = fvt.field_id
    GROUP BY p.id
    HAVING MAX(ft.id = 1 AND p2fv.field_value_id = 1) = 1
       AND MAX(ft.id = 4 AND p2fv.field_value_id = 4) = 1
)

LJ ON p2fv.product_id = LJ.pid
GROUP BY field_name, field_value;

I'm trying to get result:

|      field_name| field_value| count |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     0 |
|          Color |      Black |     1 |
|          Color |        Red |     0 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     0 |
|          Model |    2Series |     0 |
|          Model |    3Series |     1 |
|          Model |      Civic |     0 |

Answer 1

As far as i understand your question this should be the query you want:

SELECT field_name, field_value, COUNT(val.id) as count
FROM fields ft 
JOIN fields_values fvt
ON fvt.field_id = ft.id
LEFT JOIN products2fields_values val
ON val.field_value_id = fvt.id 
GROUP BY  field_name, field_value;

I don't know why you are joining your product table because it doesn't seems to be necessary for your results. And i do not understand why you implemented your strange HAVING clause.

Please take a look at the results of my query.

Answer 2

You need to correct data for field_value_id column in products2fields_values table.

With current data, Table relationships have got broken and we can't get expected results with those values sitting there.

Insert should be as below :

INSERT INTO products2fields_values
    (id, product_id, field_value_id)
VALUES
    (1,1, 1),
    (2,1, 2),
    (3,1, 3),
    (4,1, 4),
    (5,1, 5),
    (6,2, 6),
    (7,2, 7),
    (8,2, 8),
    (9,2, 9),
    (10,2, 10),
    (11,3, 11),
    (12,3, 12),
    (13,3, 13),
    (14,3, 14),
    (15,3, 15); 

Running below query as suggested by @steven should give expected results:

SELECT 
    field_name, field_value, COUNT(val.id) as count
FROM 
    fields ft 
INNER JOIN 
    fields_values fvt ON fvt.field_id = ft.id
LEFT JOIN 
    products2fields_values val ON val.field_value_id = fvt.id
GROUP BY field_name, field_value;

You can see results here