MIgrate to drupal 7 join related tables I am migrating from a another non Drupal CMS. I have 4 tables:    


stories (story1, story2, story3)    
story_tags (story_id, tag_id)    
tags (ABC, NBC, WTOP, CBS, clinton)   
tag_types (radio, tv, person)   


How can I link tags to stories by tag_type. So that when I open a story I can see all it's related tags, knowing that a story can have several tags.
Any good detailed tutorial of migrate module link will be appreciated also.   

update
How can i translate this in a migrate query   

SELECT t.name FROM tags t 
    join tag_types tt on (tt.id = 3)
    join story_tags stg on (stg.tag_id = t.id) WHERE story_id = $row_id'   


Tried but it is not working:  

-&gt;select('story_tags', 'tags')
-&gt;fields('tags', array('tag_id'))
-&gt;leftJoin('tag_types', 'tt', '(tt.id = tag.tag_type_id)')
-&gt;condition('tag_type_id', '3', '=' )
-&gt;condition('story_id', $row-&gt;id, '=')
-&gt;execute()
-&gt;fetchCol();     


What am i missing?
My approach to this would be to have a content type called Story that had 2 taxonomy term references. One tag field could just be called tags and then other could be called tag_types. These 2 fields would be 2 distinct Taxonomy Vocabularies in Drupal.

Drupal 7 actually installs with a tags vocabulary out-of-the-box which is assumed to be free-tagging terms as your legacy site appears to be already using. So you just need to make 1 vocabulary for the tag_types.

Anyways then in your migration class for Stories. Your source SQL statement would figure out the necessary terms for these 2 fields, probably using a sub-select, and the migrate module can set the terms for the content type as Stories are inserted into the system.

So, some psuedocode is:

&lt;?php

class StoryMigration extends Migration
{
  public function __construct($arguments)
  {
    parent::__construct($arguments);
    $query = Database::getConnection('default', 'legacy')
      -&gt;select('stories')
      -&gt;fields('stories', array('pgid', 'page_title', 'page_body'));
    // Create subqueries to get the tags for the story we are on. We will call
    // a fake field in our SQL query as legacy_tags or legacy_tags_type to be
    // passed using Migrate field mappings.
    $query-&gt;addExpression('SELECT GROUP_CONCAT(term_name) FROM legacydb_terms WHERE page_id = pgid' ,'legacy_tags');
    $query-&gt;addExpression('SELECT GROUP_CONCAT(term_name) FROM legacydb_term_types WHERE page_id = pgid' ,'legacy_tags_type');
    $this-&gt;source = new MigrateSourceSQL($query);
    $this-&gt;destination = new MigrateDestinationNode('page');

    $this-&gt;map = new MigrateSQLMap($this-&gt;machineName,
      array(
        'pgid' =&gt; array('type' =&gt; 'int',
          'unsigned' =&gt; TRUE,
          'not null' =&gt; TRUE,
          'description' =&gt; t('Source ID'),
        )
      ),
      MigrateDestinationNode::getKeySchema()
    );

    $this-&gt;addFieldMapping('title', 'page_title');
    $this-&gt;addFieldMapping('body', 'page_body');

    // Import a story's tag(s). Make the term in the vocabulary if it's not
    // there already, ignore the CaSe of the term so sloppy legacy terms
    // come across as 1 term.
    // By specifying this could have a SEPERATOR VALUE this can create any N
    // terms provided by our SQL statement above.
    $this-&gt;addFieldMapping('field_tags', 'legacy_tags');
    $this-&gt;addFieldMapping('field_tags:create_term')
      -&gt;defaultValue(TRUE);
    $this-&gt;addFieldMapping('field_tags:ignore_case')
      -&gt;defaultValue(TRUE);

    // Similar steps as for the above term, but for the tags_type reference field.
    $this-&gt;addFieldMapping('field_tags_type', 'legacy_tags_type');
    $this-&gt;addFieldMapping('field_tags_type:create_term')
      -&gt;defaultValue(TRUE);
    $this-&gt;addFieldMapping('field_tags_type:ignore_case')
      -&gt;defaultValue(TRUE);
  }
}


Term Reference Fields and the Migrate module are described in the documentation. Adding SQL Expressions to an SQL query is covered in the sql documentation.

My above code is an example and not guarenteed to work. It's simply 1 method of doing this and is to be referenced as an outline. Your SQL schema and php may look different in the end.