SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member cgmdeem's Avatar
    Join Date
    Jun 2004
    Location
    South Carolina
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Table join

    I have a nagging problem I've tried to solve for a while, and I hope you guys can give me a new look at this.

    2 tables:

    Table 1 is a list of "Required Skills" (just a text descrip like 'MIG WELDER')

    there can be any amount of rows in this table.

    Table 2 has 2 fields: EmpSkill(the same text as above), and an EmpID identifying the employee that has that skill.

    I want to find all the EmpID's in table 2 that have:

    A)ANY Required Skill
    B)ALL the Required Skills

    Ive been using a crazy mess of loops that is sucking the life out of me and the performance. I've been sooo very close a few times, but the real deal seems to elude me.
    Thanks in advance to all you gurus out there for any help.
    PS: I'll paypal you a cold brew if you can solve this.

    charles mayfield

  2. #2
    SitePoint Evangelist goughb's Avatar
    Join Date
    Sep 2000
    Location
    Chicago
    Posts
    526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     function has_any_skill$skill_array )
     {
         
    $empl_array = array();
     
         foreach( 
    $skill_array as $skill )
         {    
             
    $query "select table2.empid from table2, table1 where table1.empid = table2.empid and table1.skill = '$skill'
                do db
                 if( 
    $empl_returned )
                    array_push( 
    $empl_array$emplid );
         }
         return 
    $empl_array;
     }
     
     function has_all_skills( 
    $skill_array )
     {
         
    $empl_array = array();
         
    $query = "select table2.emplid from table1,table2 where table1.emplid table2.emplid"
         foreach( 
    $skill_array as $skill )
         {
            
    $query .= " and table1.skill '$skill'";
         }
         do query
         store results in 
    $empl_array
         return 
    $empl_array;
     } 

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    A) ANY Required Skill
    Code:
    select Table2.EmpID
      from Table2
    inner
      join Table1
        on Table2.EmpSkill 
         = Table1.EmpSkill
    group
        by Table2.EmpID
    B) ALL the Required Skills
    Code:
    select Table2.EmpID
      from Table2
    inner
      join Table1
        on Table2.EmpSkill 
         = Table1.EmpSkill
    group
        by Table2.EmpID
    having count(*)
         = ( select count(*)
               from Table1 )
    in case you are on some database which, you know,
    doesn't support subqueries, you may have to run the
    subquery first, get the count of all the skills, then plug
    that number into the HAVING clause

    NOTE: looping is not required!

    payment may be made here: http://r937.com/payments.cfm
    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
  •