SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    joining multiple tables

    Can someone help me fix/optimize this query?

    I'm trying to join 5 tables. Its broken up below into smaller bites and it almost works.

    TABLES
    volunteer - contains volunteer data
    partner_skills_connector - partner_id, skill_id
    volunteer_skills_connector - volunteer_id, skill_id
    partners - contains partner.id and partner.region
    volunteer_regions_connector - volunteer_id, region_id
    volunteer_partner_permissions - volunteer_id, partner_id

    The query is trying to find volunteers where the connectors match and the partner_id is not in the volunteer_partner_permissions.

    This is what I have so far:

    Code MySQL:
    CREATE TEMPORARY TABLE temp_skills 
    SELECT volunteer_skills_connector.volunteer_id
    FROM  partner_skills_connector, volunteer_skills_connector
    WHERE
    partner_skills_connector.skills_id = volunteer_skills_connector.skill_id
    AND partner_skills_connector.partner_id=1
    group by volunteer_skills_connector.volunteer_id;
     
    CREATE TEMPORARY TABLE temp_regions
    SELECT volunteer_regions_connector.volunteer_id
    FROM partners, volunteer_regions_connector
    WHERE
    partners.id=1
    AND partners.region=volunteer_regions_connector.region_id
     
    group by volunteer_regions_connector.volunteer_id;
     
     
    CREATE TEMPORARY TABLE temp_permissions
    SELECT volunteer_id
    FROM volunteer_partner_permissions
    WHERE partner_id != 1;
     
     
    CREATE TEMPORARY TABLE temp_connector
    SELECT temp_skills.volunteer_id
    FROM temp_skills, temp_regions
    WHERE temp_skills.volunteer_id=temp_regions.volunteer_id 
     
    group by temp_skills.volunteer_id;
     
    select volunteers.first_name, volunteers.last_name, volunteers.profile_image, volunteers.email
    FROM volunteers, temp_connector, temp_permissions 
    WHERE volunteers.id=temp_connector.volunteer_id
    AND volunteers.id != temp_permissions.volunteer_id
    AND temp_connector.volunteer_id != temp_permissions.volunteer_id
     
    group by volunteers.id

    Thanks E

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    maybe you could explain the logic and purpose of every one of your query statements

    we don't know your data or why you're doing what you're doing or even what you're trying to accomplish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sure

    This creates a temporary table showing volunteers with skills that match a partner that need the skills. For this example partner.id=1 is used.

    Code MySQL:
    CREATE TEMPORARY TABLE temp_skills
    SELECT volunteer_skills_connector.volunteer_id
    FROM  partner_skills_connector, volunteer_skills_connector
    WHERE
    partner_skills_connector.skills_id = volunteer_skills_connector.skill_id
    AND partner_skills_connector.partner_id=1
    GROUP BY volunteer_skills_connector.volunteer_id;

    This creates a temporary table showing volunteers with interest in a region that match a partner from that region.

    Code MySQL:
    CREATE TEMPORARY TABLE temp_regions
    SELECT volunteer_regions_connector.volunteer_id
    FROM partners, volunteer_regions_connector
    WHERE
    partners.id=1
    AND partners.region=volunteer_regions_connector.region_id
     
    GROUP BY volunteer_regions_connector.volunteer_id;

    This creates a temporary table containing volunteers that already have an association with the partner

    Code MySQL:
    CREATE TEMPORARY TABLE temp_permissions
    SELECT volunteer_id
    FROM volunteer_partner_permissions
    WHERE partner_id != 1;

    This table combines the first two temporary tables to show volunteers with both an interest in a region and have a relevant skill
    Code MySQL:
    CREATE TEMPORARY TABLE temp_connector
    SELECT temp_skills.volunteer_id
    FROM temp_skills, temp_regions
    WHERE temp_skills.volunteer_id=temp_regions.volunteer_id
     
    GROUP BY temp_skills.volunteer_id;

    This grabs actual volunteer data from the combined list and weeds out data from temp_permissions. All though the weeding out isn't working right.

    Code MySQL:
    SELECT volunteers.first_name, volunteers.last_name, volunteers.profile_image, volunteers.email
    FROM volunteers, temp_connector, temp_permissions
    WHERE volunteers.id=temp_connector.volunteer_id
    AND volunteers.id != temp_permissions.volunteer_id
    AND temp_connector.volunteer_id != temp_permissions.volunteer_id
     
    GROUP BY volunteers.id

    Thanke E

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    eruna, can you show a CREATE TABLE for each table and list what data you want from each table?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sure:

    Code MySQL:
     
     
    --
    -- Table structure for table `partner_skills_connector`
    --
     
    --Want to match skills_id to volunteer_skills_connector.skills_d
     
    CREATE TABLE IF NOT EXISTS `partner_skills_connector` (
      `partner_id` int(255) NOT NULL,
      `skills_id` int(50) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    -- --------------------------------------------------------
     
     
     
    --
    -- Table structure for table `volunteers`
    --
    --Want name, id, profile_image
     
    CREATE TABLE IF NOT EXISTS `volunteers` (
      `id` int(255) NOT NULL,
      `first_name` varchar(50) default NULL,
      `last_name` varchar(50) default NULL,
      `phone_number` varchar(30) default NULL,
      `email` varchar(200) default NULL,
      `address` varchar(255) default NULL,
      `city` varchar(100) default NULL,
      `state` varchar(2) default NULL,
      `zip` varchar(15) default NULL,
      `country` varchar(50) default NULL,
      `university` varchar(50) default NULL,
      `partner_association` varchar(100) default NULL,
      `skills` varchar(25) default NULL,
      `skills_in_detail` blob,
      `dob` varchar(10) default NULL,
      `regions_of_interest` varchar(50) default NULL,
      `about` blob,
      `profile_image` varchar(255) default NULL,
      `username` varchar(50) NOT NULL,
      `password` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `volunteer_partner_permissions`
    --
     
    -- Want to weed out any volunteers with a matching partner id
     
    CREATE TABLE IF NOT EXISTS `volunteer_partner_permissions` (
      `id` int(255) NOT NULL auto_increment,
      `volunteer_id` int(255) NOT NULL,
      `partner_id` int(255) NOT NULL,
      `email` tinyint(1) default NULL,
      `snail_mail` tinyint(1) default NULL,
      `date_added` timestamp NOT NULL default CURRENT_TIMESTAMP,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `volunteer_regions_connector`
    --
     
    -match region_id to partners.region
     
    CREATE TABLE IF NOT EXISTS `volunteer_regions_connector` (
      `volunteer_id` int(5) NOT NULL,
      `region_id` int(5) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `volunteer_skills`
    --
     
    CREATE TABLE IF NOT EXISTS `volunteer_skills` (
      `id` int(255) NOT NULL auto_increment,
      `title` varchar(255) collate latin1_german2_ci default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=45 ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `volunteer_skills_connector`
    --
     
    CREATE TABLE IF NOT EXISTS `volunteer_skills_connector` (
      `volunteer_id` int(255) NOT NULL,
      `skill_id` int(255) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Thanks!

    E

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you dasn't need the temp tables
    Code:
    /* actual volunteer data from the combined list and weeds out data from temp_permissions. */
    SELECT volunteers.first_name
         , volunteers.last_name
         , volunteers.profile_image
         , volunteers.email
      FROM volunteers
    INNER
      JOIN ( /* volunteers with both an interest in a region and have a relevant skill */
             SELECT temp_skills.volunteer_id
               FROM ( /* volunteers with skills that match a partner that need the skills. */
                      SELECT vsc.volunteer_id
                        FROM partner_skills_connector AS psc
                      INNER
                        JOIN volunteer_skills_connector AS vsc
                          ON vsc.skill_id = psc.skills_id
                       WHERE psc.partner_id=1
                      GROUP
                          BY vsc.volunteer_id
                    ) AS temp_skills
             INNER
               JOIN ( /* volunteers with interest in a region that match a partner from that region. */
                      SELECT vrc.volunteer_id
                        FROM partners AS p
                      INNER
                        JOIN volunteer_regions_connector AS vrc
                          ON vrc.region_id = p.region
                       WHERE p.id=1
                      GROUP
                          BY vrc.volunteer_id
                    ) AS temp_regions
                 ON temp_regions.volunteer_id = temp_skills.volunteer_id
             GROUP
                 BY temp_skills.volunteer_id
           ) AS temp_connector
        ON temp_connector.volunteer_id = volunteers.id
    LEFT OUTER
      JOIN ( /* volunteers that already have an association with the partner */
              SELECT volunteer_id
                FROM volunteer_partner_permissions
               WHERE partner_id = 1
           ) AS temp_permissions
        ON temp_permissions.volunteer_id = temp_connector.volunteer_id
     WHERE temp_connector.volunteer_id IS NULL
    i'm not sure the "weeding out" is working, because i don't understand your data, but everything above the LEFT OUTER JOIN should be working just as before

    your temp permissions says "volunteers that already have an association with the partner" but you have partner <> 1 which i understand to be volunteers with a relationship to any other partner except partner 1... so i changed that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'm going to try to understand this. -E

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    did it work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •