Multi Table partial text search

[radicalrod]

I have two tables, Table1 has unique ID's for chemical names, and Table2 has synonyms (none or many) for a name in Table1. The tables have thousands of entries, here is a sample:

Table1

ID(pk) Name Field1 Field2
1 Alum, cesiuun 0 2
2 Alumina 0 1
3 Mirabilite 0 1
4 Dioxin 1 1
5 Dibenzofuran 1 4

Table2
ID Synonym
1 Cesium aluminum sulfate
2 Aluminum oxide
3 Sodium sulfate
4 Polychlorinated dibenzodioxin
4 dibenzo-p-dioxin

I want to return three things: (1) fields from every row in Table1 that matches a partial text string,
(2) every row in Table1 that has a corresponding synonym (matching the partial text) in Table2, and (3) the row from Table2 that has the synonym matching the partial text, as if it were an entry in Table1.
So if I queried for the partial string "dib" only, this is the output I would like to see:

ID Name Field1 Field2

4 Dioxin 1 1
4 dibenzo-p-dioxin 1 1
5 Dibenzofuran 1 4

If I query for "sulfate" I would like to see

ID Name Field1 Field2

1 Alum, sulfate 0 2
1 Cesium aluminum sulfate 0 2
3 Mirabilite 0 1
3 Sodium sulfate 0 1

The example really helps, because the problem sounds deceptively easier than it is. Is it possible to get an output like I want? I would think such a query is possible, but it is beyond me and I am at a loss to get the output correct, thanks!

 

[JarlH]

Simply use UNION ALL to merge the results from three different SELECT's, where the SELECT's perform one task each.

Try something like this (untested):

select t1.*
from t1
where t1.name like '%dib%'
UNION ALL
select t1.id, t2.synonym, t1.f1, t1.f2
from t1 join t2 on t1.id = t2.id
where synonym like '%dib%'
UNION ALL
select t1.*
from t1
where t1.id in (select id from t2 where synonym like '%dib%')


(Perhaps not the fastest solution...)