SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Newby question

  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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

  2. #2
    SitePoint Zealot sdlifecycle's Avatar
    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

  3. #3
    SitePoint Member
    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

  4. #4
    SitePoint Zealot sdlifecycle's Avatar
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •