October 11, 2003 at 2:27 pm
Hi everybody,
Each month I send out a newsletter, which tells my members how many fellow members live in their zip code area. Presently the sql statement I'm using creates a recordset which matches zip codes from two different tables and if a member has more than 1 fellow member living in his zip code area, he ends up receiving 2 or more emails.
I want to send each member only 1 e-mail which would tell them how many fellow members live in their zipcode area. (example) You have 3 fellow members living in your area.
What would I need to add to this sql statement which would give me what I need. Or in the worst case scenario what would I need to add to eliminate having the same record appear multiple times in the recordset.
SELECT tbMembers.*, tbPcodes.*
FROM tbMembers, tbPcodes
WHERE tbMembers.ZipCode = tbPcodes.ZipCode
Thanks in advance for any help,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
October 12, 2003 at 10:32 pm
Could you explain the way (the logic)you are sending emails?
October 13, 2003 at 5:47 am
Perhaps you are looking for the COUNT(DISTINCT ColumnName) aggregate function? Or just use the primary key of the members table with a self-join, e.g.:
SELECT COUNT(m.PKCol)
FROM tbMembers m JOIN tbMembers p ON m.ZipCode = p.ZipCode
WHERE p.PKCol = @PKCol
--Jonathan
--Jonathan
October 13, 2003 at 6:25 am
About the same idea that Jonathan allready gave. Just added the Group by, so you will get the summary for each user.
SELECT p.PKCol, COUNT(m.PKCol)
FROM tbMembers m
INNER JOIN
tbMembers p
ON m.ZipCode = p.ZipCode
GROUP BY p.PKCol
October 13, 2003 at 8:50 am
Same as NP but this will return only those members that have another member in their ZipCode.
SELECT p.PKCol, COUNT(m.PKCol)
FROM tbMembers m JOIN tbMembers p ON m.ZipCode = p.ZipCode
GROUP BY p.PKCol
HAVING COUNT(m.PKCol) > 1
Alternately you could use:
SELECT p.PKCol, COUNT(m.PKPKCol
FROM tbMembers m JOIN tbMembers p ON m.ZipCode = p.ZipCode
where p.PKCol<> m.PKCol
GROUP BY p.PKCol
This might perform better depending on the indexes you have on the table.
Edited by - buddy__a on 10/13/2003 08:50:43 AM
October 13, 2003 at 9:18 am
Well, I'd go either for the Jonathan/NPeeters idea, or I'd try this to see which will perform better on your DB:
SELECT m.PKCol, m.email, (select count(tbMembers.PK) from tbMembers where tbMembers.zipcode = m.zipcode) as Count_in_area
FROM tbMembers m
Although most of the time (at least in my experience) joins have better performance than additional select, it always pays to test several versions and see which one works best.
Hope this helps,
Vladan
October 13, 2003 at 6:54 pm
Here is a simple way to get unique zipcodes as well as the number of members per zip code:
Sample tables:
create table tbMembers
(Member char(1),ZipCode char(5))
insert tbMembers (Member,ZipCode) values ('A','11111')
insert tbMembers (Member,ZipCode) values ('B','22222')
insert tbMembers (Member,ZipCode) values ('C','33333')
insert tbMembers (Member,ZipCode) values ('D','11111')
insert tbMembers (Member,ZipCode) values ('E','44444')
insert tbMembers (Member,ZipCode) values ('F','11111')
insert tbMembers (Member,ZipCode) values ('G','55555')
create table tbPcodes
(Email varchar(100),ZipCode char(5))
insert tbPcodes (Email,ZipCode) values ('A@aol.com','11111')
insert tbPcodes (Email,ZipCode) values ('B@aol.com','22222')
insert tbPcodes (Email,ZipCode) values ('C@aol.com','33333')
insert tbPcodes (Email,ZipCode) values ('D@aol.com','11111')
insert tbPcodes (Email,ZipCode) values ('E@aol.com','44444')
insert tbPcodes (Email,ZipCode) values ('F@aol.com','11111')
insert tbPcodes (Email,ZipCode) values ('G@aol.com','55555')
SELECT tbMembers.ZipCode, tbPcodes.Email,NumOfMembers=count(*)
FROM tbMembers, tbPcodes
WHERE tbMembers.ZipCode = tbPcodes.ZipCode
group by tbMembers.ZipCode,tbPcodes.Email
Charles
October 14, 2003 at 6:36 am
Agree with Cowitz ... Its a simple and easy way to get what u want....or is it that u want it in a different way than this...
Cheers!
Arvind
Arvind
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply