I'm setting up a site that allows a member to refer the site to friend or family member that hasn't either registered or been referred previously. I'm using UD4 (ASP jvscpt) with MS SQL 2000, so as you can see I'm not a code guru. Basically, I'm having a difficult time to determine the proper code (SQL) to do the following:
search the member table (member name address etc) and referred email table (foreign key to member, referred email) to see if the email that the member is referring (on Submit) is already included in either table.
I am assuming this could work with an inner join statement. Any advice or comprehensive examples you could point me to?
Okay, I'm a sucker for comprehensive examples. Please do not apologize for using UltraDev or ASP -- whatever gets the job done. I hope you will be able to incorporate this solution into your code.
Let's say your Member table looks like this --
memberPK memberName memberEmail 10 Fred firstname.lastname@example.org 20 Bob email@example.com 30 Mary firstname.lastname@example.org
And let's say your Referrals table looks like this --
memberFK referredEmail 10 email@example.com 10 firstname.lastname@example.org 10 email@example.com 30 firstname.lastname@example.org
So what we have is that Fred has referred three emails, Bob none, and Mary one.
What you said you wanted requires two separate checks. A join would not work here.
The first check is to see whether the new email being referred (let's say it's a form field variable called
select memberEmail from Members where memberEmail = &email
You have to test the number of rows returned by this query. I'm not sure which ASP variable this is, Request.RecordCount or something (I'm sorry, I don't do ASP). The query will return either no records, or one. If it's one record, stop here and issue your error message ("email is already registered").
Assuming you got no rows from the first query, you now need to do the second check. There are two ways to do this.
The first way is actually the best way, and it involves letting the database do the check for you, by declaring a unique constraint.
alter table Referrals add constraint OnlyOneReferral unique ReferredEmail
The downside of this technique is that when you go to insert the new referred email, you find out if it's already in there only when you get back a constraint violation error code from the database, and you will probably want to issue your own, friendlier error message instead.
So the second way of doing the second check is like the first check, just query the table and check the number of rows you get back --
select ReferredEmail from Referrals where ReferredEmail = &email
Ordinarily I always urge developers to use the database constraint method, but it means you have to know how to trap the resulting database error code, and some feel that the additional query (it's inefficient, that extra query) is easier to code.
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.