Som I nok kan se indeholder tabellerne noget data om manuasckripter. Jeg vil gerne hente hver manuskript fra databasen og så vil jeg hente den seneste process, som den har fået.
I process tabellen kan en manuscript forekomme mange gange, men det er den seneste jeg vil hente.
Med ovenstående SQL kommer manuskripten for hver gang den er repræsenteret i Process tabellen.
Det ser ikke ud til at virke... Denn fejl kommer for mange af felterne: Column 'Manuscript.uniqueIDCountry' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
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 GROUP BY Manuscript.m_id
SELECT Manuscript.m_id, Manuscript.uniqueIDCountry, Manuscript.uniqueIDNo, Manuscript.m_title, Manuscript.country, Manuscript.m_receivedDate, p1.p_id, p1.m_id, p1.processDate, ProcessTypes.ps_id, ProcessTypes.processName FROM Manuscript, Process p1, ProcessTypes WHERE p1.m_id = Manuscript.m_id AND p1.ps_id = ProcessTypes.ps_id AND p1.p_id = (SELECT MAX(p2.p_id) FROM Process p2 WHERE p2.m_id = p1.m_id)
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 JOIN Process ON [Manuscript].m_id = [Process].m_id JOIN ProcessTypes ON [Process].ps_id = [ProcessTypes].ps_id WHERE [Process].processDate=(SELECT MAX(processDate) FROM [Process] WHERE [Process].m_id=[Manuscript].m_id) ORDER BY uniqueIDCountry, uniqueIDNo
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
