query help

esthera · #1 (**permalink**) 05-20-07, 04:47

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?

healdem · #2 (**permalink**) 05-20-07, 05:16

have a look at the DISTINCT clause

esthera · #3 (**permalink**) 05-20-07, 05:29

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?

r937 · #4 (**permalink**) 05-20-07, 06:59

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

esthera · #5 (**permalink**) 05-20-07, 07:56

but this is returning the school collumn with null although each member has at lest one school associated with it

r937 · #6 (**permalink**) 05-20-07, 08:31

perhaps you could state what it is you're actually trying to get

why are you using LEFT OUTER JOINs?

aschk · #7 (**permalink**) 05-21-07, 05:54

Sample dataset + DDL please...

esthera · #8 (**permalink**) 05-21-07, 06:00

i figured it out -- i was missing an order by

aschk · #9 (**permalink**) 05-21-07, 07:12

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

aschk · #10 (**permalink**) 05-21-07, 07:20

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

aschk · #11 (**permalink**) 05-21-07, 07:23

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!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech