Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multi Table partial text search

Multi Table partial text search

Multi Table partial text search

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:


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

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!

RE: Multi Table partial text search

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%'
select t1.id, t2.synonym, t1.f1, t1.f2
from t1 join t2 on t1.id = t2.id
where synonym like '%dib%'
select t1.*
from t1
where t1.id in (select id from t2 where synonym like '%dib%')

(Perhaps not the fastest solution...)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close