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
[quoted text, click to view] 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 ***
