kay, oh, i wouldn't say this is simple stuff for me...i still have a lot to learn. in fact, i *just* learned that when you do a join on multiple tables like that, you either have an inner join or an outer join--not both. :) so your query is really a 'left outer join' for each table joined. try it out; you'll see that you get the same result set.
funny, i just assumed the purpose of your webuserarchive table was to store information on inactive web users. it never occurred to me you were using it to store user edit activity. i can't say i blame your admin for being paranoid about what users are up to. there's an awful lot of malicious web activity out there. thanks for your offer to help out with fusebox but...i've sort of abandoned it for now. (re: cf-talk discussion 4/30/02 late pm among dave watts, hal helms, matt liotta, et al): i have a lot of respect for dave watts, who recently commented that, in his opinion, fusebox is a relatively narrow development framework that does not address some of the bigger issues involved in web development. even before that, i had a few misgivings about the way fb3 was implemented as well as it's value as a methodology for organizing my code, so i've decided not to waste anymore time trying to learn it. besides, now that the trial version of cfmx is available for download at macromedia's website, we now have *that* to learn...and i'm very excitied about their oo approach! sorry you had a problem emailing me directly...can't imagine why. at any rate, feel free to try again sometime: [EMAIL PROTECTED] i just love trying to figure out these sql problems. :) ~ dina ----- Original Message ----- From: "Kay Smoljak" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, May 02, 2002 8:43 PM Subject: FW: sql distinct problem > Hi Dina, > > I tried to reply to you directly, but the message was bounced - > apparently my upstream ISP has been blocked by yours. > > Everyone else please excuse the following message! > > Kay. > > -----Original Message----- > From: Kay Smoljak [mailto:[EMAIL PROTECTED]] > Sent: Friday, 3 May 2002 9:23 AM > To: 'Dina Hess' > Subject: RE: sql distinct problem > > > Hi Dina, > > Thanks for all your help... looking at the scripts you are writing is > making my toes curl, and I'm sure this is simple stuff for you! I think > I saw a post from you on the Fusebox list the other day - if you ever > have any Fusebox problems I'd be pleased to help in any way I can. > > I'll explain a little about the app - it's for a subscription site. > Every couple of days the site admin will go through and run the credit > card numbers for all the subscribers that expire in the next seven days. > The query you helped me with the other day was for a page which lists > all the subscribers that are due or overdue and have been run at least > once, with unsucessful results. > > The WebUserArchive table is added to whenever a subscriber edits their > own details - the old settings are saved in this table. The reason I > wanted the last date from this table is so that the admin can look at > the list of subscribers that need re-processing, and see the last date > the details were updated - obviously if the subscriber has entered a new > credit card expiry since the last time the admin attempted the > transaction, the admin knows that they can try that subscriber again. I > don't want to remove the old WebUserArchive table when a new update is > submitted, because these guys are paranoid about fraud and they want to > know *exactly* what the subscribers are up to. > > So I don't think it's denormalized too much - maybe it's not the best > way to do it, but I don't think it's too bad. Of course I'm probably > wrong :) > > Anyway, if it's going to be too difficult - and it's looking that way - > I'll probably change the display so that instead of listing the last > updated date, it will just display a link to display *all* the > corresponding records from the WebUserArchive table. > > Thanks again for all your help, > Kay. > > -----Original Message----- > From: Dina Hess [mailto:[EMAIL PROTECTED]] > Sent: Friday, 3 May 2002 6:27 AM > To: [EMAIL PROTECTED] > Subject: sql distinct problem > > > kay, > > i forgot to mention that distinct will not work because it will only > eliminate duplicate column values; that is, if web user number one has > two records in the webuserarchive table and the value of the dtupdated > field in each of those records equals '2002/1/15,' then distinct would > work perfectly to pull just one of those values into a single record in > the result set. however, in this case, the value of dtupdated is > different in each record for the same user, so the distinct function is > doing exactly what you asked it to do. it is returning each record with > a distinctly different value in the dtupdated field. the only way (i > know of) that you can return just one of those records is to use either > the min() or max() aggregate function on the dtupdated column in the > select clause. > > but in this case, there's a caveat. this 'solution' to your > multiple-records-per-user may break if the isdisabled field is a boolean > datatype (as it appears to be). that's because you cannot group by a > boolean data type column. add to that the fact that you cannot return an > aggregate column value without first listing all non-aggregate columns > in a group by clause, and we're back to trying to figure out a > workaround for your multiple-records-per-user problem. > > at any rate, the real problem here is that your database design is > failing you. it's not normalized. if it were normalized, you wouldn't > have nine records in the same table for an archived web user. > unfortunately, as this database grows, you will experience increasingly > complex data manipulation problems with greater frequency. > > with that in mind, i'd be pleased to offer a few suggestions. if you'd > like my help, simply let me know if you're interested. > > ~ dina > > _________________________________________________________________ _____ > Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm > Archives: http://www.mail-archive.com/[email protected]/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
