Sorry about the formatting.. Grrr.. Should be in columns as: a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress, d.EMailAddress AS NewAddress
-----Original Message----- From: Jeff Beer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 20, 2002 2:29 PM To: CF-Talk Subject: SQL: self-joins and counts - lots of detail Hi folks, I'm stumped on a SQL query. Using SQL Server 7.0. I have two tables - one with a lot of detail info, and one as a cross reference. The detail table (msgs) has a primary key (message_id) and an e-mail address (emailAddress), plus other stuff that doesn't matter for now. The cross reference table (xref_forwards) has originalMessage_ID and newMessage_ID. When someone forwards an e-mail message to another person, the system currently checks to see if the new address (the forward to address) is present in the system. If so, it inserts a record into the cross reference table with the message_id of the sender, and the existing message_id of the recipient. If the new address is not present, it adds a record for that address, grabs the new message_ID, and inserts a record into the cross reference table. Pretty straightforward stuff so far. What's beating me up is writing a query that will return the original sender's email address and the people that person forwarded the message to, with a count of how many mails were forwarded per original email address. So far I have: SELECT a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress, d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID) AS totalforwards FROM xref_forwarding a INNER JOIN xref_forwarding b ON a.originalMessage_ID = b.newMessage_ID INNER JOIN msgs c ON a.originalMessage_ID = c.Message_ID INNER JOIN msgs d ON a.newMessage_ID = d.Message_ID GROUP BY a.originalMessage_ID, a.newMessage_ID, c.EMailAddress, d.EMailAddress ORDER BY a.originalMessage_ID This seems to work fine. Here's example output from the above query: (in all the output, the domain names are changed so I don't get screenscraped by spammers - the numbers all match up with the original domains). a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress, d.EMailAddress AS NewAddress 663352 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 663352 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 663352 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 663352 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 663352 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] 663353 670109 [EMAIL PROTECTED] [EMAIL PROTECTED] 663353 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 663353 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 670110 714046 [EMAIL PROTECTED] [EMAIL PROTECTED] 670110 714188 [EMAIL PROTECTED] [EMAIL PROTECTED] 671038 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 671038 663353 [EMAIL PROTECTED] [EMAIL PROTECTED] 671038 714186 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 714181 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 714182 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 714183 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 714184 [EMAIL PROTECTED] [EMAIL PROTECTED] 714066 714185 [EMAIL PROTECTED] [EMAIL PROTECTED] 714188 671036 [EMAIL PROTECTED] [EMAIL PROTECTED] 714188 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 714188 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] The counts add up fine - I tested manually, writing down who sent mail to whom. When I add a count column the results are still the same, but the counts are wrong: SELECT a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress, d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID) AS TotalForwards FROM xref_forwarding a INNER JOIN xref_forwarding b ON a.originalMessage_ID = b.newMessage_ID INNER JOIN msgs c ON a.originalMessage_ID = c.Message_ID INNER JOIN msgs d ON a.newMessage_ID = d.Message_ID GROUP BY a.originalMessage_ID, a.newMessage_ID, c.EMailAddress, d.EMailAddress ORDER BY a.originalMessage_ID outputting: a.originalMessage_ID, a.newMessage_ID, c.EMailAddress AS SenderAddress, d.EMailAddress AS NewAddress, COUNT(a.originalMessage_ID) 663352 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 663352 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 663352 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 663352 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 663352 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 663353 670109 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 663353 670110 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 663353 714066 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 670110 714046 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 670110 714188 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 671038 663352 [EMAIL PROTECTED] [EMAIL PROTECTED] 3 671038 663353 [EMAIL PROTECTED] [EMAIL PROTECTED] 3 671038 714186 [EMAIL PROTECTED] [EMAIL PROTECTED] 3 714066 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714066 714181 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714066 714182 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714066 714183 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714066 714184 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714066 714185 [EMAIL PROTECTED] [EMAIL PROTECTED] 2 714188 671036 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 714188 671038 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 714188 714187 [EMAIL PROTECTED] [EMAIL PROTECTED] 1 As you can see, the last three rows, the sender [EMAIL PROTECTED] should count 3 forwards. I thought that by grouping on the original message_id the counts would roll up as well. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
