One more - SQL query

Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Hello group
My english is very poor.
I'm beginner in sql and I've problem?
How get from table only first and last record with idPerson.
example:
Tables in SQL
EventLog
idPerson LogTime IdKomp
1 9:00 1
2 9:10 2
2 9:15 4
3 10:00 1
4 11:00 3
2 13:00 2
1 15:00 3
KompTable
IdKomp KompName
1 uranus
2 jupiter
3 mars
4 saturn
PersonTable
IdPerson PersonName
1 Jon
2 Bob
3 Tom
Select ..... ('?) order by idPerson
result:
PersonName MinTime KompName MaxTime KompName
Jon 9:00 uranus 15:00 mars
Bob 9:10 jupiter 13:00 jupiter
Tom 10:00 uranus (null) (null)
...
How join this table, meybe create temporary table.
Please help.Here is one solution.
Create a in-line view / table, which has the values you are after. Called th
is E_List.
Then join the EventLog table to it (twice), to get the details.
Then it's joining the other tables off to get the dispalyable results.
You could create the in-line view as an actual view.
SELECT P.PersonName, E1.LogTime, K1.KompName, E2.LogTime, K2.KompName
FROM ( SELECT EventLog.idPerson, MIN(EventLog.logtime) AS min_time, MAX(Even
tLog.logtime) AS max_time
FROM EventLog
GROUP BY EventLog.idPerson) AS E_List
JOIN EventLog AS E1 ON (E_List.idPerson = E1.idPerson AND E_List.Min_Time =
E1.LogTime)
JOIN EventLog AS E2 ON (E_List.idPerson = E2.idPerson AND E_List.Max_Time =
E2.LogTime)
JOIN PersonTable P ON (P.idPerson = E1.idPerson)
JOIN KompTable K1 ON (E1.idKomp = K1.IdKomp)
JOIN KompTable K2 ON (E2.idKomp = K2.IdKomp)|||Thanks LUV SQL
This is simple and work very good.
Good for You
PawelR
LUV SQL wrote:
> Here is one solution.
> Create a in-line view / table, which has the values you are after. Called
this E_List.
> Then join the EventLog table to it (twice), to get the details.
> Then it's joining the other tables off to get the dispalyable results.
> You could create the in-line view as an actual view.
> SELECT P.PersonName, E1.LogTime, K1.KompName, E2.LogTime, K2.KompName
> FROM ( SELECT EventLog.idPerson, MIN(EventLog.logtime) AS min_time, MAX(Ev
entLog.logtime) AS max_time
> FROM EventLog
> GROUP BY EventLog.idPerson) AS E_List
> JOIN EventLog AS E1 ON (E_List.idPerson = E1.idPerson AND E_List.Min_T
ime = E1.LogTime)
> JOIN EventLog AS E2 ON (E_List.idPerson = E2.idPerson AND E_List.Max_T
ime = E2.LogTime)
> JOIN PersonTable P ON (P.idPerson = E1.idPerson)
> JOIN KompTable K1 ON (E1.idKomp = K1.IdKomp)
> JOIN KompTable K2 ON (E2.idKomp = K2.IdKomp)