Error - trying to get only latest

Hi

I'm trying to get some data fra a few tables, but I'm having a few
problems.

What I would like is this:

The tables contain somn info on manuscripts and which process that
manuascript has received.

a manuscript is represented once in manuascript table
that manuascript can have several records in the process table.

What I want is to get data for each manuscript and the last process that
the manuscript received from the process table.

This SQL gets the manuscript for each process it has received. SO if a
manuscript has 5 process records I will get 5 records back...

SELECT
Manuscript.m_id, Manuscript.uniqueIDCountry,
Manuscript.uniqueIDNo, Manuscript.m_title,
Manuscript.country, Manuscript.m_receivedDate,
Process.p_id, Process.m_id, Process.processDate,
ProcessTypes.ps_id, ProcessTypes.processName
FROM
Manuscript,
Process,
ProcessTypes
WHERE [Process].m_id = [Manuscript].m_id
AND [Process].ps_id = [ProcessTypes].ps_id

Please help... I'm getting desperate..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try (untested)

SELECT
M.m_id,
M.uniqueIDCountry,
M.uniqueIDNo,
M.m_title,
M.country,
M.m_receivedDate,
P.p_id,
P.m_id,
MAX(P.processDate) AS ProcessDate,
PT.ps_id,
PT.processName

FROM
Manuscript M
LEFT JOIN Process P
ON M.M_Id = P.M_Id
LEFT JOIN ProcessTypes PT
ON P.PS_Id = PT.PS_Id

GROUP BY
M.m_id,
M.uniqueIDCountry,
M.uniqueIDNo,
M.m_title,
M.country,
M.m_receivedDate,
P.p_id,
P.m_id,
PT.ps_id,
PT.processName

I've taken a guess that the max of the ProcessDate from your process
table will give you the last record that you want. You may need to
look at which columns are appropriate for giving you the correct
record, your column names aren't the most helpful hence the guess. The
left join is what you were missing though.

I've not added in anything for your third table and assumed that it is
a stright one to one relationship, but you should easily see how to do
anything further. I've also added some table aliases and tidied things
up a bit to make it easier to read.

Hope that helps

Ryan

Thomas Stark <stark@.newidentity.dk> wrote in message news:<41777eca$0$24956$c397aba@.news.newsgroups.ws>...
> Hi
> I'm trying to get some data fra a few tables, but I'm having a few
> problems.
> What I would like is this:
> The tables contain somn info on manuscripts and which process that
> manuascript has received.
> a manuscript is represented once in manuascript table
> that manuascript can have several records in the process table.
> What I want is to get data for each manuscript and the last process that
> the manuscript received from the process table.
>
> This SQL gets the manuscript for each process it has received. SO if a
> manuscript has 5 process records I will get 5 records back...
> SELECT
> Manuscript.m_id, Manuscript.uniqueIDCountry,
> Manuscript.uniqueIDNo, Manuscript.m_title,
> Manuscript.country, Manuscript.m_receivedDate,
> Process.p_id, Process.m_id, Process.processDate,
> ProcessTypes.ps_id, ProcessTypes.processName
> FROM
> Manuscript,
> Process,
> ProcessTypes
> WHERE [Process].m_id = [Manuscript].m_id
> AND [Process].ps_id = [ProcessTypes].ps_id
>
> Please help... I'm getting desperate..
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks a lot.

Yeah thats pretty much what I was looking for.

I've spent hours trying to sort it out.. Maybe I should try and learn a
bit more SQL..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!