Hello Peter, I agree I don't seem to be explaining what I want properly.
Let me offer some data: (hopefully this will come out ok in the message). hitID sessionID userID time date pageName 251 2917000 3591528 6:22:17 2003-7-2 News Section 250 2917000 3591528 6:22:6 2003-7-2 News Section 249 2917000 3591528 6:22:2 2003-7-2 News Section 248 2917000 3591528 6:21:53 2003-7-2 News Section 247 2917000 3591528 6:21:48 2003-7-2 News Section 246 2769572 3630081 6:21:2 2003-7-2 Slider : Pharma Facts 245 2769572 3630081 6:21:0 2003-7-2 Slider : Pharma Facts 244 3281651 3630081 6:20:10 2003-7-2 Slider : SmartSample Benefits 243 9198624 7495400 5:56:3 2003-7-2 Physician : ExpressSample 242 6250098 7495400 5:51:16 2003-7-2 Investor Login 241 3053989 7495400 5:22:47 2003-7-2 Patient : When dealing with Meds 240 3208231 930881 5:20:42 2003-7-2 Corporate Services Login 239 5815985 7206699 5:19:17 2003-7-2 News Section 238 5358098 637853 5:17:50 2003-7-2 Payer : Reduces Costs 237 5909188 637853 5:17:33 2003-7-2 Pharma Company : Pharma Companies 236 7699639 951681 5:16:22 2003-7-2 Slider : Mission Statement 235 7699639 951681 5:16:13 2003-7-2 Slider : CSNM 234 6259004 951681 5:15:27 2003-7-2 Pharma Company : Ready, Set, Go 233 2592554 9502072 5:4:59 2003-7-2 Physician : ExpressSample 232 4721794 9502072 5:3:32 2003-7-2 Pharma Company : Ready, Set, Go 231 4721794 9502072 5:3:22 2003-7-2 Slider : Mission Statement 230 3053989 7495400 4:59:40 2003-7-2 About STI : Board of Directors 229 3053989 7495400 4:59:25 2003-7-2 Slider : Pharma Facts 228 2236829 7495400 4:58:9 2003-7-2 Slider : CSNM 227 2236829 7495400 4:56:39 2003-7-2 Slider : Paragon So this is the data. Lets say I want my query to give me the sessions for userID 7495400. As you can see there are lots of userID's of that number listed in the table and they are sometimes associated with different sessionID's. The sessionID's are what I'm after. Now the way I had the query was SELECT DISTINCT sessionID, date,time FROM sti_tracking WHERE userID=7495400 But what it gives me is this: sessionID time date pageName 9198624 5:56:3 2003-7-2 Physician : ExpressSample 6250098 5:51:16 2003-7-2 Investor Login 3053989 5:22:47 2003-7-2 Patient : When dealing with Meds 3053989 4:59:40 2003-7-2 About STI : Board of Directors 3053989 4:59:25 2003-7-2 Slider : Pharma Facts 2236829 4:58:9 2003-7-2 Slider : CSNM 2236829 4:56:39 2003-7-2 Slider : Paragon Note the multiple instances of the sessionID's What I want returned is this: sessionID time date pageName 9198624 5:56:3 2003-7-2 Physician : ExpressSample 6250098 5:51:16 2003-7-2 Investor Login 3053989 5:22:47 2003-7-2 Patient : When dealing with Meds 2236829 4:58:9 2003-7-2 Slider : CSNM I don't really care which sessionID in relation to date/time it chooses as long as it's consistent. I just want a general idea of date and time (really date is always going to be the same for each session). Mike H gave me a great solution using multiselects but unfortunate the version of mysql I'm working with doesn't support that (3.23). Maybe this isn't possible with only mySQL. Perhaps I have to do some work with the data in PHP as well (which Mike also suggested). One last search for a mySQL answer and then I'll hunker down for a coding solution. Thanks everyone who responded. Tim Winters Manager, Creative Development Sampling Technologies Incorporated (STI) [EMAIL PROTECTED] [EMAIL PROTECTED] W: 902 450 5500 C: 902 430 8498 -----Original Message----- From: Peter K Aganyo [mailto:[EMAIL PROTECTED] Sent: July 2, 2003 8:00 PM To: [EMAIL PROTECTED] Subject: Re: Newbie SELECT problem Tim: Assuming that in your ealier posting the 99 was supposed to be 999, then the solution given by Mike Hillyer is excellent and should work. However, when I read your new posting, I seem to get confused. The scenario sounds totally different - excuse me - from the earlier one and would therefore need a different solution. You might help us by giving sample data. Or is this what you mean by "But I don't want duplicate session numbers (one is enough)"? ==> In a single session (sessionID) user 999 (userID 999) may visit 3 pages. This results in three inserts being made into table sti_tracking all having same sessionID and userID. Correct? When retrieving you do not want to retrieve all these three records. Correct? You just want one of the records. Which one? The first, second or third because they each probably have a different time and pageName (even date!!). If you did not want the date, time and pageName then the solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking WHERE userID = 999. If you do not care which of the entries (3 in my example) is returned and you still want the date, time and pageName (my guess is the first will be returned), then you need to generate all the distinct userID and sessionID pairs using the above SQL. Then for each pair (use a loop) run SELECT userID, sessionID, date, time, pageName FROM sti_tracking WHERE userID = {provide from loop} AND sessionID = {provide from loop} LIMIT 1. Peter Aganyo Tim Winters wrote: >Hello, > >Very sorry to everyone about the confusing message. I should have read >it over again before pressing send. > >First of all I'm looking for userID 999. A typo in the message not in >the code. > >The table is set up like this. > >Table name "sti_tracking" > >hitID (primary key) (autonumber) >userID >sessionID >date >time >pageName > > >What it's for is a simple page tracing counter for a FLash site. Each >time a section is accessed a new row is written in the table. > >userID identifies the user. So if the user comes to the site today and >comes back again tomorrow the userID will be maintained. > >sessionID identifies 1 visit to the site. During 1 visit a user may >view many sections within the site but as long as he doesn't close the >browser the session number remains the same. Date and time will always >be different (as will the hitID obviously). > >So what I want to be able to do is single out a user (999) and retrieve >all the sessions he was involved in. But I don't want duplicate session >numbers (one is enough). > >Make any more sense? > > > >Tim Winters >Creative Development Manager >Sampling Technologies Incorporated > >1600 Bedford Highway, Suite 212 >Bedford, Nova Scotia >B4A 1E8 >www.samplingtechnologies.com >[EMAIL PROTECTED] >[EMAIL PROTECTED] >Office: 902 450 5500 >Cell: 902 430 8498 >Fax:: 902 484 7115 > > >-----Original Message----- >From: William R. Mussatto [mailto:[EMAIL PROTECTED] >Sent: July 2, 2003 2:13 PM >To: [EMAIL PROTECTED] >Subject: Re: Newbie SELECT problem > > > >>Hello everyone, >> >>I have the following select statement >> >>SELECT DISTINCT sessionID, userID, date, time >>FROM sti_tracking >>WHERE userID = 999 >> >>What I want is to have only records with the userID of 99 and where >> >> >the > > >>sessionID is distinct (meaning only on of each session id). Neither >>sessionID nor userID are keys or unique. >> >>Obviously this isn't working. >> >>Can someone suggest how this should be done? >> >>Tim Winters >>Creative Development Manager >>Sampling Technologies Incorporated >> >> >Had a similar experience, and I've been doing it long enough to know >better. 'DISTINCT' would work only if "date" and "time" returned the >same >values. > >Are '999' and '99' supposed to be the same? >Let me see if I can rephrase what you are looking for: >a. For user '999' give me the information where there is only one >record >with a given SessionID? > >b. For user '999' for each sessionID give me the unique Date and Time >values. > >c. something else entirely. > >Also, are you running this in a procedureal language (e.g., perl, java)? >This will give us other options. > > >William R. Mussatto, Senior Systems Engineer >Ph. 909-920-9154 ext. 27 >FAX. 909-608-7061 > > > > > -- Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with [EMAIL PROTECTED] http://shopnow.netscape.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
