SQL Statement help needed!
-
Hi Folks,
I run a babysitter referral service for parents in the US and Canada and following is the problem I am having.
I have a database which has 2 tables as follows.
Table one is called tbParents which stores various contact information for my parent members including their zip code. The column name which stores their zip code is pZipCode and the primary key is pUserID
Table two is called tbPcodes which stores the zip codes which babysitters provide their services in. The column name which stores which zip code babysitters provide their services in, is called pcMyZips and the primary key is bsID
I send out a monthly e-mail to my parent members which advises/reminds parents that a babysitter is available. The problem is presently my SQL statement which creates the recordset my mailing script uses, will have multiple parent records for the same parent if there is more than one babysitter listed who provides their services in his zip code area.
So in other words. If we have a parent who lives in zip code area 90210 and we have 3 babysitters which service 90210. He will receive 3 e-mails telling him that there are babysitters available. I just want to send him 1 email. So what I want to do is remove duplicate parent records from the recordset.
Following is the present SQL statement I am using.
SELECT tbParents.*, tbPcodes.*
FROM tbParents, tbPcodes
WHERE tbParents.pZipCode = tbPcodes.pcMyZips AND tbPcodes.bsID > MMColParam AND tbPcodes.bsID < MMColParam2
ORDER BY pUserID DESC
Note: The AND statement only separates the total number of records into a more manageable size for e-mailing purposes.
What do I need to add to this statement to remove the duplicate parent records?.
Thanks in advance for any help.
Robert
By the way I am using a sql2000 database.
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide! -
SELECT distinct tbParents.* FROM tbParents, tbPcodes
WHERE tbParents.pZipCode = tbPcodes.pcMyZips AND tbPcodes.bsID > MMColParam AND tbPcodes.bsID < MMColParam2
ORDER BY pUserID DESC
tbPcodes.* has to go because they are different against the same tbParents entry.
If you do need tbPcodes.* , you have the pcMyZips code already(tbParents.pZipCode = tbPcodes.pcMyZips) .
-
Thanks very much wz700, it worked like a charm.
Now that you have helped me accomplish that function, Could you tell me if it would be possible to calculate the number of duplicate records each parent record has, and add the value to the recordset.
If possible, I would then be able to dynamically add the number of babysitters each parent has available to them in the e-mail.
I.E. John you have (recordset result) babysitters available which offer their services in your area.
Instead of my intended - John you have 1 or more babysitters available which service your area.
Thanks Again,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide! -
SELECT tbParents.*, count(tbPcodes.pcMyZips) FROM tbParents, tbPcodes
WHERE tbParents.pZipCode = tbPcodes.pcMyZips AND tbPcodes.bsID > MMColParam AND tbPcodes.bsID < MMColParam2
group by tbParents.*
ORDER BY pUserID DESC
You may have to expand tbParents.* to each individual column name.
-
wz700. Once again you were bang on and the statement worked fine, but regretfully my program won't pickup the new column created by the count function because it's not defined in the recordset and it has no name.
Oh well I guess you can't have it all?
Thanks again,
Rob
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide! -
Just add a column-header to the count, as in:
SELECT tbParents.*, Sitters=count(tbPcodes.pcMyZips) FROM tbParents, tbPcodes
WHERE tbParents.pZipCode = tbPcodes.pcMyZips AND tbPcodes.bsID > MMColParam AND tbPcodes.bsID < MMColParam2
group by tbParents.*
ORDER BY pUserID DESC
-
Thanks gdefi, between wz700 and yourself you both made my day!.
The statement works perfectly and functions exactly as intended. It will certainly add a professional touch to my monthly e-mail updates, and has taught me a little bit more about working with SQL.
Thanks again to the both of you - It's very much appreciated.
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide! -
Interesting homepage you have, Robert.
Are you going to have a German branch?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
It's not very likely. I am thinking of adding the UK and Australia to my service, but I would not venture outside of providing services for countries which do not have english as their first language . It would just be to difficult to do the language translations.
Thanks for the comment,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide! -
If I remember correct from history lessons it was only one single vote some centuries ago that you now speak english and not german .
Good luck with your business.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Robert,
If you venture into UK and Australia, note the differences in Postcodes.
In the UK, the full postcode, if I am not mistaken, indicates the "block" as you say it in the US, e.g. CR4 1QG indicates maybe a few houses in an area serviced by the Croydon Post Office. Therefore, a breakdown in the codes, i.e. using just CR4, would be useful for your purposes.
In Australia, you can have one postcode for two distinct municipal divisions. They are always 4 digit codes.
Keep up the good work.
Cheers,
Gabriel.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply