Join that returns always 1 record

Question

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,

Answer 1

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

Select B.PERS, T.DESC
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

Answer 2

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

---

Another way, similar to the above in logic but with different join syntax:

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 t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> 
          ( SELECT ti.type
            FROM TestTable AS ti
            WHERE ti.grp = 'G1'
              AND ti.test = d.test
              AND ti.type = b.type
       --   ORDER BY (ti.type IS NULL)
              LIMIT 1
          ) 
ORDER BY 
    b.pers,
    d.test ;