SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Manchester
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find members with multiple jobs (Query related problem)

    I'll cut right to it.

    I have 2 general tables:

    1) members with fields: ID_Member and Name
    2) jobs with fields: ID_Job and Title

    I have a 3rd table that links the two:

    3) members_jobs with fields: ID_Member_Job, ID_Member, and ID_Job

    How do I write a query that would be able to check for a Member (ID_Member) who has 2 or more Jobs (ID_Job)? I.e. I want the query to return all Members who work as programmers and engineers.

    Thanks,

    Bardi

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum. See Where should I post my thread? for details. Also, please use descriptive thread titles

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  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)
    Code:
    select m.ID_Member 
         , m.Name
      from jobs as j
    inner
      join members_jobs as  mj
        on j.ID_Job = mj.ID_Job
    inner 
      join members as m
        on mj.ID_Member = m.ID_Member
     where j. Title in ('programmer','engineer')
    group
        by m.ID_Member 
         , m.Name
    having count(*) = 2
    p.s. your members_jobs table would be better without the ID_Member_Job column -- make the primary key a composite of the other two columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Manchester
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry Sean. Won't happen again.

    r937 - it works a charm. Only a little problem.. What about if I want the query to only return people who do all the jobs selected as opposed to one or more of the jobs selected?

  5. #5
    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)
    then just make sure the number in the HAVING clause is equal to the number of jobs in the WHERE clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Manchester
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    then just make sure the number in the HAVING clause is equal to the number of jobs in the WHERE clause

    Heh works a treat, but it brings me to one last question (promise!)..

    What do I do if I want to search based on more than 1 criteria. By that I mean, we add an additional table to the equation. So:

    1) members with fields: ID_Member and Name
    2) jobs with fields: ID_Job and Title
    3) qualifications with fields: ID_Qualification and Title

    Which will lead to another table being added here for 3):

    4) members_jobs with fields: ID_Member_Job, ID_Member, and ID_Job
    5) members_qualifications with fields: ID_Member_Qualification, ID_Member, and ID_Qualification

    What would the query to get all members who are assigned as 'programmers' + 'engineers' for jobs who have 'BSc' and 'Masters' for qualifications be? I've tried duplicating the join bits to no avail. And, whatever additions you make, should I just duplicate them if I was to add another table or more to the query call?

    Much, much appreciated!

  7. #7
    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)
    Code:
    select jt.ID_Member
         , jt.Name
      from (     
           select m.ID_Member 
                , m.Name
             from jobs as j
           inner
             join members_jobs as  mj
               on j.ID_Job = mj.ID_Job
           inner 
             join members as m
               on mj.ID_Member = m.ID_Member
            where j.Title in ('programmer','engineer')
           group
               by m.ID_Member 
                , m.Name
           having count(*) = 2 
           ) as jt
    inner
      join (     
           select m.ID_Member 
                , m.Name
             from qualifications as q
           inner
             join members_jobs as  mq
               on q.ID_Qualification = mq.ID_Qualification
           inner 
             join members as m
               on mq.ID_Member = m.ID_Member
            where q.Title in ('BSc','Masters')
           group
               by m.ID_Member 
                , m.Name
           having count(*) = 2 
           ) as qt
        on jt.ID_Member
         = qt.ID_Member
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Manchester
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    SELECT jt.ID_Member, jt.Name FROM 
     
    (select m.ID_Member, m.Name FROM global_jobs as j inner join members_jobs as mj on j.ID_Job = mj.ID_Job inner join members as m on mj.ID_Member = m.ID_Member where j.ID_Job in ('1','4') GROUP BY m.ID_Member , m.Name HAVING count(*) = 2 ) as jt 
     
    inner join 
    ( SELECT m.ID_Member , m.Name FROM global_qualifications as q inner join members_qualifications as mq on q.ID_Qualification = mq.ID_Qualification inner join members as m on mq.ID_Member = m.ID_Member where q.ID_Qualification in ('1','2') GROUP BY m.ID_Member , m.Name HAVING count(*) = 2 ) as qt on jt.ID_Member = qt.ID_Member
    Okay, unfortunately, I tried the query but I got a SQL syntax error :

    'You have an error in your SQL syntax near 'select m.ID_Member, m.Name FROM global_jobs as j inner join members_jobs as mj o' at line 1'

    I tried doing a seperate query using the contents within both sets of brackets, and the queries indiviually ran fine. Any ideas what might be going wrong here?


  9. #9
    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)
    yes, the error indicates that you cannot use subqueries, which means you're not on 4.1 yet (which has been in production status since october)
    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
  •