mysql - 在左连接中过滤数据

Question

这是一些数据:

record
-------------------------------------------------
| id      | name                                |
-------------------------------------------------
| 1       | Round Cookie                        |
| 2       | Square Cookie                       |
| 3       | Oval Cookie                         |
| 4       | Hexagon Cookie                      |
-------------------------------------------------

record_field_data
----------------------------------------------
| id      | record_id | data       | type_id |
----------------------------------------------
| 1       | 1         | White      | 1       |
| 2       | 1         | Round      | 2       |
| 3       | 2         | Green      | 1       |
| 4       | 2         | Square     | 2       |
| 5       | 3         | Blue       | 1       |
| 6       | 3         | Oval       | 2       |
| 7       | 4         | Hexagon    | 2       |
----------------------------------------------

record_type_field
-------------------------------------------------
| id      | data_type                           |
-------------------------------------------------
| 1       | Color                               |
| 2       | Shape                               |
-------------------------------------------------

我正在尝试获取与类型为“Color”的 record_field_data 连接的所有记录的列表。这需要是左连接，因为可能没有给定类型的 record_field_data，如果是这种情况我仍然想要记录。

这是我提出的查询，但它返回的是包含所有 record_field_data 的左连接，而不仅仅是我想要的特定数据。

SELECT record.id AS id, recordfield.data, recordtype.field_name
FROM record
LEFT JOIN record_field_data AS recordfield ON (record.id = recordfield.record_id)
LEFT JOIN record_type_field AS recordtype ON (recordfield.type_id = recordtype.id AND recordtype.data_type = 'Color');

我可以使用 JOIN 中的子查询来执行此操作，但我不能使用子查询。我必须将其转换为 HQL，并且 HQL 不支持连接的子查询。

我要查找的结果是按 record_field_data 排序的记录，其中 record_type_field.data_type 是“Color”。请注意，“Hexagon cookie”没有定义颜色，此时我不知道它应该在顶部还是底部。无论哪种方式都会起作用。

    -------------------------------------------------
    | id      | name                                |
    -------------------------------------------------
    | 3       | Oval Cookie                         |
    | 2       | Square Cookie                       |
    | 1       | Round Cookie                        |
    | 4       | Hexagon Cookie                      |
    -------------------------------------------------

Answer 1

SELECT  r.id, r.name
FROM    record r
JOIN    record_type_field rf
ON      rf.data_type = 'Color'
LEFT JOIN
        record_type_data rd
ON      rd.record_id = r.id
        AND rd.type_id = rf.id
ORDER BY
        rd.data