SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Select Help

  1. #1
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Help

    Hi All,

    i have 2 tables namley Members & assingments

    members:
    id
    name
    etc

    Assingments
    id
    member_id
    status
    etc

    I need to display all the members that do not have an assingment, or if the assingment status = 2;

    I have the following query
    PHP Code:
    SELECT members.* FROM members LEFT JOIN assignments ON (members.id assignments.member_id)
                       
    WHERE assignments.status != '1' OR assignments.member_id IS NULL ORDER BY members.surname ASC 
    This works fine if the member is not assiinged but if the assingment is set to 2, and a new assingment is set to 1 (active) then it still displays the member because there is a 2 in the assingments table

    Isn't there a easy way of fixing this query ?

    Thanks

    K-

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by The Mog
    This works fine if the member is not assiinged but if the assingment is set to 2, and a new assingment is set to 1 (active) then it still displays the member because there is a 2 in the assingments table
    this is your problem right here. you explaination isn't clear enough. can you provide some sample data?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select members.* 
      from members 
    left 
      join assignments 
        on assignments.member_id = members.id
     where assignments.member_id is null 
        or assignments.status = 2
    order 
        by members.surname asc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok let me try and explain a little better....

    here is some data in the Members Table

    id | surname | firstname
    1 | France | Kenny
    2 | Blogs | Joe

    Here is some data in the Assignments Table

    id | member_id | project_id | status
    1 | 1 | 1 | 2
    2 | 1 | 2 | 2
    3 | 1 | 3 | 1
    4 | 2 | 4 | 2

    Ok so Member 1 has a active Project, But member 2 has completed his project
    I only want Member 2 to be displayed.

    I hope this is more clear

    Thanks

    K-

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, so you want members who have no corresponding rows in the assignments table, or only rows with status 2, right?
    Code:
    select m.id
         , m.surname
         , m.firstname
      from members m
     where 2 = all
           ( select distinct
                    status
               from assignments
              where member_id = m.id )
        or not exists
           ( select 1
               from assignments
              where member_id = m.id )
    this quersy should be pretty fast if you create an index on assignements(member_id, status)

  6. #6
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Longneck

    Thanks a Million that is simply pure genius

    K-

  7. #7
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    id | surname | firstname | company
    1 | France | Kenny | 1
    2 | Blogs | Joe | 2

    Here is some data in the Assignments Table

    id | member_id | project_id | status
    1 | 1 | 1 | 2
    2 | 1 | 2 | 2
    3 | 1 | 3 | 1
    4 | 2 | 4 | 2

    Code:
     
    
    select m.id , m.surname , m.firstname from members m where 2 = all ( select distinct status from assignments where member_id = m.id ) or not exists ( select 1 from assignments where member_id = m.id )
    Ok the above code is 100% correct but i want to add an additional filter,

    WHERE company = '1'

    Any idea on how to do that?

    Thanks

    K-

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    which table is company in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the members table


    Thanks

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select members.id 
         , members.surname 
         , members.firstname
         , members.company 
      from members 
    left 
      join assignments 
        on assignments.member_id = members.id
     where members.company = 1
    group 
        by members.surname 
         , members.firstname
         , members.id
         , members.company 
    having sum(
            case when assignments.status = 1
                 then 1 else 0 end
              ) = 0
    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
  •