join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-13 : 09:28:17

below is my sql statement

SELECT Members.*, Members_schools.Is_Primary, schools.school FROM (Members_schools LEFT JOIN Members ON Members_schools.Member_ID = Members.Member_ID) RIGHT JOIN schools ON Members_schools.school_ID = schools.ID WHERE (((Members_schools.Is_Primary)=1))

i have a members table then I have a members_schools table which as more then 1 school per member but only one record that IS_Primary=1

now i wnat my query to return all members and their primary school

what am i doing wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 09:34:28

try


SELECT Members.*, Members_schools.Is_Primary, schools.school 
FROM  Members_schools 
LEFT  JOIN Members ON Members_schools.Member_ID = Members.Member_ID AND Members_schools.Is_Primary = 1
RIGHT JOIN schools ON Members_schools.school_ID = schools.ID 
WHERE Members_schools.Is_Primary = 1

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-13 : 09:47:05

no this is returning 1 record per school but not all members

i want it to return all the members adn with the members a field called school with their primary school (if there is one - if not return school null)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 10:14:06

maybe post some sample records and the result that you want

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-13 : 10:27:21

i want it to return list of members and school so for exmaple

john doe school1
kelly Jane
John Jane school3
Jay Shmoe school1
John shmoe school1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-13 : 11:05:13

No RIGHT joins! that's a clue that your FROM clause is wrong. Always start FROM your primary table, in this case Members, if you want to return all members. Then LEFT OUTER JOIN to your auxiliary tables.


select 
  M.*, MS.IsPrimary, S.School
from 
  Members M
left outer join 
  members_Schools MS on MS.Member_ID = M.Member_ID AND MS.Is_Primary = 1
left outer join
  Schools S on MS.SchoolID = S.SchoolID

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources