mysql - Filtering data in left join -

mysql - Filtering data in left join -

here data:

record ------------------------------------------------- | id | name | ------------------------------------------------- | 1 | round cookie | | 2 | square cookie | | 3 | oval cookie | | 4 | hexagon cookie | ------------------------------------------------- record_field_data ---------------------------------------------- | id | record_id | info | type_id | ---------------------------------------------- | 1 | 1 | white | 1 | | 2 | 1 | round | 2 | | 3 | 2 | greenish | 1 | | 4 | 2 | square | 2 | | 5 | 3 | bluish | 1 | | 6 | 3 | oval | 2 | | 7 | 4 | hexagon | 2 | ---------------------------------------------- record_type_field ------------------------------------------------- | id | data_type | ------------------------------------------------- | 1 | color | | 2 | shape | -------------------------------------------------

i trying list of records left joined record_field_data of type "color". needs left bring together because there may not record_field_data of given type, , still want record if case.

this query have come returning left bring together record_field_data , not specific ones want.

select record.id id, recordfield.data, recordtype.field_name record left bring together record_field_data recordfield on (record.id = recordfield.record_id) left bring together record_type_field recordtype on (recordfield.type_id = recordtype.id , recordtype.data_type = 'color');

i subquery in bring together can't utilize subquery. have translate hql , subqueries not supported in hql joins.

the result looking records ordered record_field_data record_type_field.data_type 'color'. note "hexagon cookie" doesn't have color defined, don't know if should @ top or bottom @ point. either way work.

------------------------------------------------- | id | name | ------------------------------------------------- | 3 | oval cookie | | 2 | square cookie | | 1 | round cookie | | 4 | hexagon cookie | -------------------------------------------------

select r.id, r.name record r bring together record_type_field rf on rf.data_type = 'color' left bring together record_type_data rd on rd.record_id = r.id , rd.type_id = rf.id order rd.data

mysql sql join