April 25th, 2013, 09:40 AM
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.
HotSpotAccont is where i get my log ins from
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,
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
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
April 25th, 2013, 09:51 AM
Sounds like Big Brother is watching!
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: