I have heard good things about the books "Teach yourself SQL in 10 minutes" and "Database Design for Mere Mortals".
What you are trying to do is a basic inner join.
I would have expected to see a query that could look something like.
SELECT apppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status, Train ID, AppIDFK, Training Date FROM AppInfo INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.APPFK) ORDER By LName, FName
This query should return all the combined records.
Filterering this down to the most recent training record can be added once the combination has been properly returned.
I'm getting closer I think. I've applied your suggested code and without the WHERE clause it returned all TrainingDates for everyone and then I added the following and it returned one record which is the Max Date for all regardless their group. That reminds me, shouldn't I have GROUP BY somewhere in this query?
SELECT AppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status,
NewDate, TrainID, AppIDFK, TrainingDate
INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.AppIDFK)
WHERE TrainingDate = (SELECT Max(TrainingDate)
ORDER BY LName, FName
Not if you use a sub-query like that.
You could forgo the sub-query and use the MAX operator in the SELECT clause of the main query, you would then need a group-by clause listing all fields not used in the MAX() opeator.
SELECT aField, bField, cField, MAX(dField)
GROUP BY aField, bField, cField
You can continue to use the sub-query, it might look something like:
FROM aTable aaa INNER JOIN bTable bbb (aTable.key = bTable.key)
WHERE aField = (SELECT MAX(aField) FROM aTable WHERE aTable.aKey = aaa.aKey)
You either don't need the subquery or you have it in the wrong place.
If all those other fields are not in the same table as training date, you don't need the subquery. Put max(trainingdate) in your select clause and add a group by clause.
Otherwise, your subquery should be in the from clause, not the where clause. Something like this
select TrainingDate, etc
from appinfo ap join traininfo tr on (a.appidpk = tr.appidpk)
join (select appidpk id, max(trainingdate) maxdate
group by appidpk) sq on tr.appikdpk = id and trainingdate = maxdate
Thanks Dan for your response. I'm trying to implement it but need some clarification. On the Where clause, is that where I would check if it matches the AppIDPK field and if so, how would I write that? So far this is what I have but I'm stuck.
select AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate
from AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK)
join (select AppIDFK, max(TrainingDate) maxdate, TrainID
group by TrainID) on AppIDFK = AppIDPK and TrainingDate = maxdate
Here's my first table layout.
AppIDPK FName LName 1 Jane Doe 2 Janice Doe
Here's my 2nd table layout.
TrainID AppIDFK TrainingDate 1 1 05/12/2006 2 2 05/11/2008 3 1 07/13/2008
I'm trying to only retrieve the most recent training date for each name.
You have already done it with "on AppIDFK = AppIDPK".
Any where clause would deal with date ranges and other things to get just the results you want. For performance reasons, whatever you contstrain on inside your subquery should be repeated outside your subquery. And example would be
where trainingdate between date1 and date2
I've used the syntax below and I'm getting a SQL Server error "Incorrect syntax near the keyword 'on' ". As for the where clause, I'm not checking for a TrainingDate within a date range. I'm just wanting the most recent (Max) training date for each name so do I still have to include a where clause?
BTW, thanks for all the suggestions thus far.
SELECT AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate
FROM AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK) JOIN
(SELECT AppIDFK, Max(TrainingDate) MaxDate, TrainID
GROUP by TrainID) on AppIDFK = AppIDPK
ORDER BY LName, FName
Unfortunately, the CF 8 debugging output isn't telling me much either and doesn't show the sql being ran.
When you have a subquery in your from clause, it needs an alias.