Newby question

[istranger]

Hi there,

First the disclaimer - I am VERY new at using MySQL - so this question may seem overly simple, but I work in a small shop and using dbas is not an option for now :-).

I have a database with 2 tables that contain 3 fields of interest:

The "people" table contains "FullName", the "Event" table contains "EventID and EventTime"

I can get a report to come out in this format:

Jim..........Logged In..... 2:30 PM
Jim..........Logged out.... 3:00 PM
etc.

What I want is:
________Logged In_Logged_Out
Jim 2:30 PM 3:00 PM

From what I have found on the net I gather this needs to be done with pivot tables? But I don't have enough MySQL expertise to get it working.

Any help would be appreciated.

Jim

[sdlifecycle]

Are those the only fields on the 2 tables? It sounds like you need an ID field to link Events to People table. Once you figure that out, you can do a JOIN query.

example
select a.FullName, b.EventTime
from People a inner join Event b
on a.PeopleId b.PeopleId

[istranger]

oh sorry ... forgot that part - I have this is how far I got (see below). The "EventTypeCode" lets me narrow in on the two "EventIDs" that I talked about, while IpSource and IpPerson are the the key fields.

This works, but it gives me the results I showed earlier (vs. the pivot style I am looking for).

Thanks

Jim

select e.EventTime, p.FullName, e.EventID
from Event e, Person p
where e.EventID = 'Login'
and e.EventTypeCode='USERACCESS'
and e.IpSource = p.IpPerson

[sdlifecycle]

And do you have a field that says whether its a Login or Out? Is that EventID? Then, try something like this:

select e.EventTime, p.FullName, e.EventID
from Event e, Person p, event e2
where e.EventID = 'Login'
and e.EventTypeCode='USERACCESS'
and e.IpSource = p.IpPerson
and e.EventId = 'Logged_In'
and e2.EventID = 'Login'
and e2.EventTypeCode='USERACCESS'
and e2.IpSource = p.IpPerson
and e2.EventId = 'Logged_Out'
order by p.FullName, e.EventTime