Find the record that exists in multiple tables

Question

I have a table Record (id, name). Then I have 10 different Method tables in each one there are few sub-methods.

So my tables look like:

One:

Record (id, name)
1, abc
2, fgt
3, ght

Two:

Method A (id, rec_id, meth_name)
1, 3, sub1_metha
2, 3, sub2_metha
3, 2, sub1_metha
4, 1, sub3_metha
....

Three:

Method Z,(id, rec_id, meth_name)
1, 2, sub1_methz
2, 1, sub4_methz
3, 2, sub3_methz
4, 3, sub1_methz

User needs to be able to search by sub_methods with either AND or ANY operand.

I need to create 2 queries:

To select all records that have all submethods specified by user.
To select all records that have any submethods specified by user.

I feel like I'm missing something very obvious here (UNION, JOIN, 10 SELECTs)?

There's `WHERE` and `IN` and/or `FIND_IN_SET` that you can look into, as well as sub-queries. — Funk Forty Niner, May 20 '14 at 14:38
You could make a view with all method tables, and filter on that view. — sridesmet, May 20 '14 at 14:41
See this Q: on SO with the accepted answer http://stackoverflow.com/q/23752318/ it may help. If it does, +1 the accepted or other answer given on that page. — Funk Forty Niner, May 20 '14 at 14:42
@Gudgip not sure what View is here, but will read about it too. Thanks! — Victoria B, May 20 '14 at 14:43

Stefan Winkler · Accepted Answer · 2014-05-21T05:02:12.223

If you really have similar information in ten different tables, then your database design is a bit weak. Perhaps you should have only one table for sub_methods and have an additional column to distinguish between them:

Method (id, rec_id, type, meth_name)
1, 3, A, sub1_metha
2, 3, A, sub2_metha
3, 2, A, sub1_metha
4, 1, A, sub3_metha
....
1, 2, Z, sub1_methz
2, 1, Z, sub4_methz
3, 2, Z, sub3_methz
4, 3, Z, sub1_methz

But with the design you have, you should go ahead and join the record table with the union of all method tables. Then you can add the user query in the WHERE either using OR or AND, depending on which mode the user wants:

SELECT * FROM record
INNER JOIN (
SELECT id, rec_id, 'A' as type, meth_name FROM Method_A
UNION
SELECT id, rec_id, 'Z' as type, meth_name FROM Method_Z
) methods on record.id = methods.rec_id
WHERE ...;

EDIT (alternative solution using a database view):

As suggested by Gudgip: If you already have your table structure and cannot change it anymore, you can create a VIEW to simplify the above query (and potentially other queries). To do this execute this statement once:

CREATE VIEW all_methods AS 
SELECT id, rec_id, 'A' as type, meth_name FROM Method_A
UNION
...
UNION
SELECT id, rec_id, 'Z' as type, meth_name FROM Method_Z;

As a result you get an "alias" for this construct at database level and your search-query can be written as

SELECT * FROM record
INNER JOIN all_methods on record.id = all_methods.rec_id
WHERE ...;

He could create a view out of all his record-tables :-) that might ease up the work. — sridesmet, May 20 '14 at 14:46

Find the record that exists in multiple tables

1 Answers1