Most recent record in table linked to another table?!?

[Coatezy]

Hi,

Sorry if this doesn't make sense..

Just building a database at the moment that logs conversations. I have one table called job data that holds job info such as job id, start date, end date etc etc.. Then another table linked with a relationship to this called convo data that holds the conversation info/history; convo id, convo time, convo date and convo. For each job there could be a number of converstion records..

What I want to do is display every active job and against that show the last convo id that is linked to each job.. I will then look at the time that the convo record was created and if over 2 days old use an update query to mark the job as closed.

Hmmm hope that all kind of makes sense... Would something like this be possible..?

Thanks in advance guys!

 

[anthonys]

Basically you need to first establish the latest conversation for each job (bottom select) and then use the results of that query to restrict your main query

Something like this..........

SELECT DISTINCT
J1.Field1, J1.field2, C1.Field1 etc.......
FROM Jobs AS J1 INNER JOIN
Convo AS C1 ON J1.id = C1.Jobs_id
WHERE (J1.active = Yes) AND (C1.convo_date =
(SELECT MAX(C1.Date) AS Expr1
FROM Convo AS C1
WHERE (CONVO.id = J1.convo_id)))

Ant

 

[duluter]

Ant,

What you wrote works, but isn't it a little confusing to give two separate table instances in your query the same alias ("C1")? (Even though they are both referring to the same table.) I don't know much about SQL naming conventions. Is this standard?


Duluter