Select within a select

[jeepxo]

I've been asked to add some more information to a web page.
The original select statement works fine, my problem is in the change that I have to make.

Basically I think that I need to do a select based on the results of the first select.

Here's the statement.

SELECT employer.name, employer.address1, employer.address2, employer.city, employer.postalcode, employercontact.ContactID, employercontact.FirstName, employercontact.LastName, employerContact.phoneNumber , employercontact.Extension, employerContact.emailAddress, employerContact.Comments, employerContact.faxNumber, employer.schoolID, schools.name AS schoolname, LearningPlan.LearningPlanID AS myLearningPlan ,


(select Schools.Name FROM  (((schools INNER JOIN teachers on schools.id=teachers.schoolID) INNER JOIN  students ON students.teacherID = Teachers.teacherID) INNER JOIN LearningPlan ON learningplan.studentID=students.studentID) WHERE  


LearningPlan.LearningPlanID =  myLearningPlan )


as myLearningPlanSchool




FROM (((Employer LEFT JOIN Employercontact on Employer.EmployerID = EmployerContact.EmployerID) LEFT JOIN schools on Employer.SchoolID = Schools.ID) LEFT JOIN LearningPlan on LearningPlan.contactID = EmployerContact.contactID) 

WHERE employer.employerid = 9738

 ORDER BY employercontact.ContactID ASC

I've separated the selects to make it easier to see where I'm at.

I need to take the LearningPlanID from the main select and use it for my where clause in the sub select.

Is this possible or do I need to separate it into a second select statement? My preference is not to do this because having to iterate through the results of the first select to get the values for the second would really slow down the generating of the html page.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[dbomrrsm]

Simplistic but this is what you need to do with the above:

Select A,B,C,D,E
FROM TableA
WHERE LearningPlanID IN
(SELECT LearningPlanID From TableB)

[blue]DBomrrsm[/blue]

 

[tlbroadbent]

It is a little more complex than dbomrrsm's solution. I recommend not using the correlated sub-query or sub-select statement. Use joins as in the following.

SELECT emp.name, emp.address1, emp.address2, emp.city,
 emp.postalcode, ec.ContactID, ec.FirstName,
 ec.LastName, ec.phoneNumber , ec.Extension,
 ec.emailAddress, ec.Comments, ec.faxNumber,
 emp.schoolID, sch.[name] AS schoolname,
 lp.LearningPlanID AS myLearningPlan, 
 sch2.[name] As myLearningPlanSchool

FROM Employer emp
LEFT JOIN Employercontact ec on emp.EmployerID = ec.EmployerID
LEFT JOIN schools sch on emp.SchoolID = sch.[ID] 
LEFT JOIN (LearningPlan lp on ec.contactID = lp.contactID
INNER JOIN LearningPlan lp2 ON lp.LearningPlanID = lp2.LearningPlanID
INNER JOIN students s ON lp2.studentID = s.studentID
INNER JOIN teachers t ON s.teacherID = t.teacherID
INNER schools sch2 ON t.schoolID = sch2.[id])

WHERE emp.employerid = 9738
ORDER BY ec.ContactID ASC

I have no way to test this but it should be close to what you need.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[jeepxo]

It's actually an access database not SQL, but DBAs don't hang out in the access forum.

The Join that you suggested Terry isn't supported in Access, but that is exactly the type of answer that I am looking for.



"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001