Problem solve Get help with specific problems with your technologies, process and projects.

Checking a referred e-mail against two tables

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        fred@example.com
   20       Bob         bob@example.com
   30       Mary        mary@example.com

And let's say your Referrals table looks like this --

memberFK  referredEmail
   10     foo@example.com
   10     bar@example.com
   10     qux@example.com
   30     auntsusy@example.com

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 &email) is that of someone who is already a member --

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.