Not simple: SELECT b.pers, t.description FROM BaseTable AS b CROSS JOIN ( SELECT DISTINCT test FROM TestTable WHERE grp = 'G1' ) AS d LEFT JOIN TestTable AS ti ON ti.grp = 'G1' AND ti.test = d.test AND ti.type = b.type LEFT JOIN TestTable AS t ON t.grp = 'G1' AND t.test = d.test AND t.type <=> ti.type ORDER BY b.pers, d.test ; Tested at [SQLfiddle][1] [1]: http://sqlfiddle.com/#!2/3eb92/3