Composite key queries in MySQL

Question

I have a table called Modules with columns (M_Id, Name, Version, Descr). I have another table detailing dependencies for each modules, i.e. a module may be dependent upon another. For this I use a table called Mod_depends which has the composite key (M_Id_1, Depends). Example entries:

Module
1 | Chameleon | 2.9.2 | HTML/XML template engine
2 | Unittest2 | 1.0.0 | unittest module
3 | OrderedDict | 1.0.1 | Ordered dictionary

Mod_depends
1 | 2
1 | 3

I'm looking for a MySQL query that, for a given id, can return entries from the Modules table representing the dependencies. In the example above, supplying Id = 1 would return:

2 | Unittest2 | 1.0.0 | unittest module
3 | OrderedDict | 1.0.1 | Ordered dictionary

Does this require a join or some subquery magic or both?

Is there a way I can add constraints such that additions to the Mod_depends table is only possible when an entry exists in then Modules table?

Cheers

Does it have to return the second-, third- and so on order dependencies? For example, if module 2 is in turn dependent on module 4, should query for module 1 return module 4 as its dependency as well? — raina77ow, Aug 28 '12 at 15:50

score 0 · Answer 1 · answered Aug 28 '12 at 15:51

Well, you didn't give the names of your fields, so I just made some up that should hopefully be explanatory. Basically you join on the id in teh module table and the dependency id (right column) in the mod_depends table, then lookup using the module id (left column) in the mod_depends table like this:

SELECT m.module_id, m.module_name, m.module_version, m.module_description FROM module as m INNER JOIN mod_depends as md on m.module_id = md.module_dependency_id
WHERE md.module_id = 1;

score 0 · Accepted Answer · edited May 23 '17 at 10:24

For the simplest case (first-order dependencies only) it's something like this:

    SELECT m.M_id, m.Name, m.Version, m.Descr
      FROM Module AS m
INNER JOIN Mod_depends AS md
        ON m.M_id = md.Depends
     WHERE md.M_Id_1 = 1;

... but it'd be a really non-trivial task to find more deep dependencies, as it'll become a query for tree-like structure. So you can either use this discussion for ideas, or reorganize your DB from Adjacency List to Closure Table. Perhaps you'd want to check this wonderful answer as well. )

Composite key queries in MySQL

2 Answers2