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!