I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value).
I need a join to this table on the test and type but so that if no type is specified I get the generic tests (That is no problem as both sides have NULL) but if a type is specified I get the test of that type if there is one, or the generic test when there exist no specific one.
TestTable ... Test Grp Type Description ... ... TestA G1 NULL TestA_Gen ... ... TestA G1 M TestA_M ... ... TestB G1 NULL TestB_Gen ... ... TestB G1 X TestB_X ... BaseTable ... Pers Type ... ... P_A Null ... ... P_B M ... ... P_C X ... Select B.PERS, T.DESCRIPTION from BaseTable B Join Testtable T on ??? and T.Grp = 'G1' should give P_A TestA_Gen P_A TestB_Gen P_B TestA_M P_B TestB_Gen P_C TestA_Gen P_C TestB_X
How do I do that in MySQL?
Many thanks in advance,