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 :
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
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 ;