Select all products with prices in EAV scheme

Question

It's a generic SQL question. I have a query that selects all rows from the Products with extra information from other tables. The problem is that it's EAV scheme and the last relation is somehow reversed and joins break.

The requirements are:

list all Products with Groups
if 'Price' in Values table is available, add this information
explicitly: if 'Price' is not available, there should be Product row without Price information
Products can't be repeated
Additionally: DISTINCT is out of question

I have a working query (below) that uses a subquery to filter values, but I need to get rid of it. I can only uses joins.

SQL Fiddle : http://sqlfiddle.com/#!15/0576b/8

create table Products (
    id int primary key,
    groupId int,
    code varchar(100)
);

create table Groups (
    id int primary key,
    code varchar(100)
);

create table Values (
    id int primary key,
    productId int,
    typeId int,
    value varchar(100)
);

create table ValueTypes (
    id int primary key,
    name varchar(100)
);

insert into Products values (1, 1, 'P1');
insert into Products values (2, 2, 'P2');
insert into Groups values (1, 'C1');
insert into Groups values (2, 'C2');
insert into Values values (1, 1, 1, 'Aqua');
insert into Values values (2, 1, 2, '$5');
insert into ValueTypes values (1, 'Name');
insert into ValueTypes values (2, 'Price');

My query that works:

SELECT *
FROM Products p
INNER JOIN Groups g ON p.groupId = g.id
LEFT JOIN Values v ON v.productId = p.id AND v.typeId = (SELECT id FROM ValueTypes WHERE name = 'Price')

The question is, how to rewrite it to use joins instead of subquery?

I tried:

SELECT *
FROM Products p
INNER JOIN Groups g ON p.groupId = g.id
LEFT JOIN Values v ON v.productId = p.id
LEFT JOIN ValueTypes vt ON vt.id = v.typeId AND vt.name = 'Price'

But it returns repeated product P1. INNER JOIN on the other hand omits Products without a 'Price' value.

Serg Serg · Accepted Answer · 2016-06-07T20:07:37

Define JOIN order explicitly

SELECT *
FROM Products p
INNER JOIN Groups g ON p.groupId = g.id
LEFT JOIN (  --product price
  SELECT productId, value  
  FROM Values v2
  JOIN ValueTypes vt ON vt.id = v2.typeId AND vt.name = 'Price'
) v ON v.productId = p.id;

EDIT
2 more JOIN versions. Optimizer produces different plan as compared to above version

SELECT *
FROM ValueTypes vt
INNER JOIN Products p ON vt.name = 'Price'
INNER JOIN Groups g ON p.groupId = g.id
LEFT JOIN Values v ON v.productId = p.id AND v.typeId = vt.id;

or slightly different v3

SELECT *
FROM (SELECT id FROM ValueTypes WHERE name = 'Price') vt
CROSS JOIN Products p 
INNER JOIN Groups g ON p.groupId = g.id
LEFT JOIN Values v ON v.productId = p.id AND v.typeId = vt.id

Select all products with prices in EAV scheme

2 Answers