#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    18
    Rep Power
    0

    Help with multi table Query


    I am trying to combine three separate queries into one. I want to return the date, time and web address visited by users between the times they were logged in.
    I have the query below which finds out log in times but im not sure how to adjust it to retrieve web addresses between the log in times it finds.

    Code:
    SELECT T1.aDate, T1.aTime, T2.aDate, T2.aTime, DATEDIFF(n, T1.aDate+T1.aTime, T2.aDate+T2.aTime) AS Duration FROM 
    (SELECT aDate, aTime, userIP FROM HotSpotAccount
     WHERE aUser = ? AND Message Like '%logged in%' ORDER BY aDate ASC, aTime ASC)AS T1,
    HotSpotInfoDebug T2
     WHERE T2.Ip = T1.userIP AND T2.Message Like '%logged out%' 
    AND T2.Username = ? AND T1.aDate+T1.aTime < T2.aDate+T2.aTime ORDER BY T1.aDate ASC, T1.aTime ASC
    HotSpotAccont is where i get my log ins from
    HotSpotInfoDebug is where i get my log outs from
    WebProxy is the table where my web addresses are the relevant columns in that table are
    aDate, aTime, WebAddress and UserIP

    Any help appreciated
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Sounds like Big Brother is watching!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    without a SHOW CREATE TABLE, i'm not sure what all of these columns are (particularly the distinction between aUser and userIP), but assuming those are both unique ids of the user, it would be something like this:
    SET @username=?;
    SELECT T1.aDate, T1.aTime, T2.aDate, T2.aTime, WA1.Webaddress,
    DATEDIFF(n, T1.aDate+T1.aTime, T2.aDate+T2.aTime) AS Duration
    FROM
    (SELECT aDate, aTime, userIP
    FROM HotSpotAccount
    WHERE aUser = @username AND Message Like '%logged in%' ORDER BY aDate ASC, aTime ASC)
    T1
    JOIN HotSpotInfoDebug T2 on T1.userIP=T2.IP
    JOIN WebAddress WA1 on T1.userIP=WA1.userIP
    WHERE T2.Username = @usernameAND T2.Message Like '%logged out%'
    AND T1.aDate+T1.aTime < T2.aDate+T2.aTime
    and WA1.aDate+WA1.aTime BETWEEN T1.aDate+T1.aTime and T2.aDate+T2.aTime
    ORDER BY T1.aDate ASC, T1.aTime ASC

IMN logo majestic logo threadwatch logo seochat tools logo