> I think your problem is you didn't alias the query to join to try:

SELECT * FROM studentinfo RIGHT JOIN  (SELECT distinct wsucaseid , max(ID) 
AS vID,  max(visitDate) AS vDATE  FROM VISIT WHERE visit.ID  IN (SELECT 
EdGoalType.VISITID FROM EdGoalType WHERE (((EdGoalType.GoalType)=1 Or 
(EdGoalType.GoalType)=4))) group by wsucaseid) as QryVisit ON 
studentinfo.wsucaseid=qryVisit.wsucaseid

(note since you use right join you will only get students that have a 
visit,
use left join if you want student regardless of whether they have a visit 
(a
record in the second query).

Brian Freeman
(770) 916-0595 ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: Jeff Sorrels [mailto:jdsorrels@m...]
Sent: Monday, December 30, 2002 6:08 PM
To: sql language
Subject: [sql_language] I need help with a Join!


I need to just pull all fields from the first table here, and I need to 
pull all of the tables from the subquery.  The first table is called 
studentinfo.  Here are the queries broken up.



1. ---> SELECT * FROM studentinfo

2. ---> 

SELECT DISTINCT [wsucaseid], max([ID]) AS vID, max([visitDate]) AS vDATE
FROM VISIT
WHERE visit.ID  IN (SELECT EdGoalType.VISITID FROM EdGoalType WHERE 
(((EdGoalType.GoalType)=1 Or (EdGoalType.GoalType)=4)))
GROUP BY [wsucaseid];


3. --->  My attempt at a right join...I keep getting an error in Join 
Operator...it hightlights the word visit in [max(visitDate)]...I have no 
idea!!!



SELECT * FROM studentinfo RIGHT JOIN  (SELECT distinct wsucaseid , max(ID) 
AS vID,  max(visitDate) AS vDATE  FROM VISIT WHERE visit.ID  IN (SELECT 
EdGoalType.VISITID FROM EdGoalType WHERE (((EdGoalType.GoalType)=1 Or 
(EdGoalType.GoalType)=4))) group by wsucaseid) ON 
studentinfo.wsucaseid=visit.wsucaseid


Please Help...as I am at my wits end!
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to