Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '11 at 20:52
    
@Marian thanks for pointing that out. I have flagged it as a duplicate –  David May 11 '11 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 '11 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'. –  David May 11 '11 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 '11 at 21:48

3 Answers 3

up vote 2 down vote accepted

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;
share|improve this answer
    
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. –  David May 12 '11 at 13:24

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.

share|improve this answer
    
Seems reasonable to me, +1. –  RolandoMySQLDBA May 13 '11 at 11:42

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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