2

Background

In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL.

I would like to know how (if) I can simplify join statements, e.g. by setting default fields to join on.

My tables are named with a plural form of the object that they contain:

 names
 types
 actions
 surnames
 names_surnames

where names_surnames is a many-many lookup table

the primary keys are always id and the foreign keys are always, e.g.

 names.type_id
 names.action_id
 names_surnames.name_id
 names_surnames.surname_id

My joins would be something like

select names.col1 
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;

Question

Is there a way to get MySQL to always assume that tables will be joined with on thistables.thattable_id = thattable.id?

7
  • I think it was already treated in this question: Are there any database engines which will intuit the join condition based on the existing foreign keys?.
    – Marian
    May 11, 2011 at 20:52
  • @Marian thanks for pointing that out. I have flagged it as a duplicate May 11, 2011 at 21:00
  • @David, do those answers work for you sufficiently enough that I should close this as a duplicate or do you believe this answer should stand on its own merit as a legitimate question?
    – jcolebrand
    May 11, 2011 at 21:02
  • @jcolebrand I think this question is distinct enough that it could stand on its own merit if it had a distinct answer, but without an answer, this question is not really distinct from the other. Based on the other question, the answer to both questions seems to be 'no, not without a custom script'. May 11, 2011 at 21:14
  • @David ~ So would you have found that question in any way other than your being told here on this question about it? If not, then I'm going to let it stand so that others can find the same information. We can merge/delete later if need be.
    – jcolebrand
    May 11, 2011 at 21:48

3 Answers 3

3

How about creating views for joins that are used repeatedly ?

For example:

create view my_view as 
select names.id as name_id, names.type_id, names.action_id, ...
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;
1
  • I think that this will do the trick, thank you for introducing me to this. The whitepaper 'mysql 5.0 views' is a more comprehensive treatment of the topic. May 12, 2011 at 13:24
3

If the columns were named the same in "both" tables, I think you could use NATURAL JOIN. Although that won't work for you, your consistency in naming should still let you generate queries--or at least the join clauses--with a scripting language.

1
2

If the tables involved in the JOIN share identically named fields,
then you can use the USING clause of the JOIN syntax.

For example

IF the actions table had action_id as the primary key instead of id
and the types table had type_id as the primary key instead of id

THEN the query could be rewritten as

select names.col1 
from names join types USING (type_id)
           join actions USING (action_id);

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.