database select query probem

Nishad85

Hi I have 3 tables in my database. Students , SessionTable and Campus. Table campus have two fields CampusId and CampusName and SessionTable have two fields SessionId and Sessioname and the tble student have12 fields they are studentId,Name,DOB,Grade,Phone,City, campus1,Session1, campus2,Session2, campus3,Session3 I want tao take all the rows of following fields from tables STUDENTNAME,STUDENTID,DOB,GRADE,PHONE,CITY,Campus.CAMPUSNAME AS CAMPUS1,SessionTable.SESSIONNAME AS SESSION1,CAMPUS.CAMPUSNAME AS CAMPUS2,SessionTable.SESSIONNAME AS SESSION2,CAMPUS.CAMPUSNAME AS CAMPUS3,SessionTable.SESSIONNAME AS SESSION3 I wrote a query like this . But does not takes all the rows from Database SELECT Students.StudentID, CampusList_1.Name As Campus1, CampusList_2.Name As Campus2 , CampusList_3.Name As Campus3, Students.Session1,Students.Session2,Students.Session3,Students.StudentName as FirstName,Students.StudentLastName as LastName, Students.DateOfBirth, Students.Street,Students.AppartmentNo,Students.City, Students.State, Students.ZipCode, Students.Phone, Students.ParentFirstName, Students.ParentLastName, Students.SSN, Students.SchoolYear, Students.Semester, iif(Students.Sibling,'Yes','No')as Sibling,iif(Students.Accepted,'Yes','No')as Accepted,iif(Students.Waitlisted,'Yes','No')As Waitlisted ,Students.ApplicationDate,Students.CurrentSchool,Students.AltenatePhone From ((( Students LEFT JOIN CampusList AS CampusList_1 ON Students.Campus1 = CampusList_1.CampusID) LEFT JOIN CampusList AS CampusList_2 ON Students.Campus2 = CampusList_2.CampusID) LEFT JOIN CampusList AS CampusList_3 ON Students.Campus3 = CampusList_3.CampusID )LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session1 LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session2 LEFT JOIN SessionTable ON SessionTable.SessionId=Students.Session3 Pls help me on this issue..........

andyharman

Try:

SELECT Students.StudentID, CampusList_1.Name As Campus1,
   CampusList_2.Name As Campus2, CampusList_3.Name As Campus3,
   Students.Session1, Students.Session2, Students.Session3,
   Students.StudentName as FirstName,
   Students.StudentLastName as LastName,
   Students.DateOfBirth, Students.Street, Students.AppartmentNo,
   Students.City, Students.State, Students.ZipCode, Students.Phone,
   Students.ParentFirstName, Students.ParentLastName, Students.SSN,
   Students.SchoolYear, Students.Semester,
   iif(Students.Sibling, 'Yes', 'No') as Sibling,
   iif(Students.Accepted, 'Yes', 'No') as Accepted,
   iif(Students.Waitlisted, 'Yes', 'No') As Waitlisted,
   Students.ApplicationDate, Students.CurrentSchool,
   Students.AltenatePhone
FROM Students
LEFT JOIN CampusList AS CampusList_1
   ON Students.Campus1 = CampusList_1.CampusID
LEFT JOIN CampusList AS CampusList_2
   ON Students.Campus2 = CampusList_2.CampusID
LEFT JOIN CampusList AS CampusList_3
   ON Students.Campus3 = CampusList_3.CampusID
LEFT JOIN SessionTable AS Session_1
   ON Session_1.SessionId = Students.Session1
LEFT JOIN SessionTable AS Session_2
   ON Session_2.SessionId = Students.Session2
LEFT JOIN SessionTable AS Session_3
   ON Session_3.SessionId = Students.Session3
ORDER BY Students.StudentID