0

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,

0

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
  • Sorry, I didn't had some notification of the answers. – FilMar Feb 27 '15 at 17:23
  • This query is not correct. It returns more records than expected. – FilMar Feb 27 '15 at 17:31
0

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.