Mysql – Join that returns always 1 record


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.

    ... Test   Grp  Type  Description ...
    ... TestA  G1   NULL  TestA_Gen    ...
    ... TestA  G1   M     TestA_M     ...
    ... TestB  G1   NULL  TestB_Gen   ...
    ... TestB  G1   X     TestB_X     ...

    ... Pers  Type ...
    ... P_A   Null ...
    ... P_B   M    ...
    ... P_C   X    ...

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,

Best Answer

According to given sample data there is "Type" which is common in both table

from BaseTable B
right outer Join Testtable T 
on ( T.type is null or B.Type=T.type or B.Type is null)
and T.Grp = 'G1' 
where T.DESC<>'' 
order by B.PERS asc