Results 1 to 11 of 11

Thread: query help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: query help

    I have the following sql

    select M.*, MS.Is_Primary, S.school from Members M left outer join Members_schools MS on MS.Member_ID = M.Member_ID left outer join schools S on MS.school_ID = S.id where isactive=1 and (MS.school_ID=4)

    now in MembersSchools - I have more then one school per member but when I return the result I want to be able to search through all of the records for the member for the school in the where clause but in the result actually only return 1 record per member and not multiple records as I am doing now.

    Is this possible?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the DISTINCT clause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    but my problem is I need it distinct by all the collumns and I want them all to be returned in my query not just one of them.
    how can i do this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in a LEFT OUTER JOIN, any conditions you have on the right tables should go into the ON clause, not the WHERE clause
    Code:
    select M.*
         , MS.Is_Primary
         , S.school 
      from Members M 
    left outer 
      join Members_schools MS 
        on MS.Member_ID = M.Member_ID 
       and MS.school_ID = 4
    left outer 
      join schools S 
        on S.id = MS.school_ID
     where M.isactive = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    66
    but this is returning the school collumn with null although each member has at lest one school associated with it

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you could state what it is you're actually trying to get

    why are you using LEFT OUTER JOINs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Sample dataset + DDL please...

  8. #8
    Join Date
    Jun 2006
    Posts
    66
    i figured it out -- i was missing an order by

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    From what I can see you should NOT be using left outer join on schools because you are using the school_ID in your where clause. So not joining on a school (i.e. left join) is pointless because you ARE looking for a school id. So ruling out those using a normal JOIN is better.

    So it should at LEAST be :
    Code:
     JOIN schools S on MS.school_ID = s.id

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In fact I believe it can be re-written without a distinct clause also and using subselects instead.

    Code:
    select M.*
         , (SELECT MS.Is_Primary 
            FROM Members_schools MS 
            JOIN schools S ON S.id = MS.School_ID
            WHERE MS.Member_ID = M.Member_ID AND S.id = 4)
         , (SELECT S.school FROM schools S WHERE S.id = 4)
      from Members M 
    WHERE M.Member_ID IN (
      SELECT Member_ID
      FROM Members_schools
      WHERE school_ID = 4)
    AND M.isactive = 1

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    However, having said all that. I believe you are approaching (expanding) your query from the wrong end.

    From the looks of it what you are after is all the members for a particular school... easy peasy when you think about it that way. Thus in fact your query should be :

    Code:
    SELECT S.school
             , MS.IsPrimary
             , M.*
      FROM school S
      JOIN Members_schools MS ON MS.School_ID = s.id
      JOIN Members M ON M.Member_ID = MS.Member_ID
    WHERE s.id = 4
    Et voila!!!!

Posting Permissions

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