Got an odd query I'm trying to figure out... A hotel chain client is running a promotion. They've put up ad banners, and sent out emails, all linking back to a particular page that logs the traffic. Each link has a reference code that ties to what part of the promotion the user is responding to. After that, there's a list of hotels with special rates - when a user clicks on one of these, this is also logged, tied to the record in the first table through an ID. What I'm trying to do is code a query to pull out all the distinct reference codes, and then count up the number of records in the second table, divvied up by the hotel code, that tie back to each reference code. So say table A reads: AID Code -- ---- 1 CodeA 2 CodeA 3 CodeB 4 CodeC and table B reads: BID AID HotelCode --- --- --------- 1 1 HotelB 2 2 HotelC 3 3 HotelB 4 4 HotelA I'd want to set up a query to spit out a count field for EACH Code in table A, with a count of clicks that tie to it in table B - including having a zero count for those combos that, while possible, don't actually exist. Thoughts? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
