SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Newby question
-
Oct 16, 2009, 17:49 #1
- Join Date
- Oct 2009
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Newby question
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
-
Oct 16, 2009, 18:01 #2
- Join Date
- Sep 2006
- Location
- Minnesota
- Posts
- 129
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Oct 16, 2009, 18:11 #3
- Join Date
- Oct 2009
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Oct 21, 2009, 17:26 #4
- Join Date
- Sep 2006
- Location
- Minnesota
- Posts
- 129
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks