SQL Query

[BDakis]

I am not a database administrator or any such thing, and I apologize beforehand if my question is too basic for this forum.
A family member is attempting to set up some sort of website where people will be registering and he needs to be able to run a query against the existing database whenever someone submits their information. The point is, he wants to make sure a given person is not attempting to join twice. If the name and address or name and phone number (or some combination thereof) are the same the page should report back to the user that they already exist in the db.
As I said, I am not a db admin or anything, but I "work with computers" and as such am expected to know every and anything related!
Thanks in advance for any assistance.

-Bill.

 

[DotNetGnat]

something like this...

'declare connection object
'declare recordset object

sql="SELECT memberID FROM Members WHERE mname='"&request.form("memadd")&"' AND maddress='"&request.form("address")&"' AND mphone='"&request.form("phonenum")&"' "

rs.Open SQl, conn

If rs.EOF AND rs.BOF then

--you can go ahead and add this user

else

-user already exists...

end if


-DNG

 

[DaOtH]

Bill,

Try something like:
(The declare/set paert you might want to omit, depentant on your code.)

DECLARE @EquipNum Numeric(10,0)
DECLARE @Range Int
SET @EquipNum = 2346732048
SET @Range = 2

SELECT
   Equipnum, EquipStatus 
FROM
   tblEquipment
WHERE 
   Equipnum BETWEEN @EquipNum AND @EquipNum + @Range
GROUP BY 
   Equipnum, EquipStatus
HAVING 
   (
      SELECT
         COUNT(Equipnum) 
      FROM
         tblEquipment
      WHERE 
         Equipnum BETWEEN @EquipNum AND @EquipNum + @Range
    ) = @Range

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[DaOtH]

XCuz me, wrong post

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[BDakis]

Thanks guys! I didn't think this would be too difficult. I will pass this along!

 

[Warlocks]

Bill,

One way would be to use something like:

Let's say you are checking for:
Name = Fred
Address = High Street
Phone = 123456

select <field_list>
from <table_name>
where
((PersonName = 'Fred' and PersonAddress = 'High Street')
or
(PersonName = 'Fred' and PersonPhone = '123456')
or
(PersonPhone = '123456' and PersonAddress = 'High Street'))

A better way would be to use a stored procedure in the database and return the results that way:

create procedure myPersonCheck

@PName varchar(30),
@PAdd1 varchar(30),
@PPhone varchar(30)

as

select <field_list>
from <table_name>
where
((PersonName = @PName and PersonAddress = @PAdd1)
or
(PersonName = @PName and PersonPhone = @PPhone)
or
(PersonPhone = @PPhone and PersonAddress = @PAdd1))


Obviously if the address is split up into add1, add2 etc then you can check these also where checking the address.
The idea is just a basic maths/logic idea of using parentheses to break up the comparison.

Hope this helps.

Woody.

 

[SQLBill]

BDakis,

Is your family member using a Microsoft SQL Server database? If not, the solutions provided via this forum (for Microsoft SQL Server: Programming) may not help. Not all SQL is the same.

-SQLBill

Posting advice: FAQ481-4875

 

[Warlocks]

Bill,

DotNetGnat's query is something that would be built using ASP (i.e. the application code).
My script was the actual SQL to use.

The only difference in the script is that DotNetGnat is checking that ALL 3 conditions are met and not some permutation of the 3 values you suggested.

Combine the 2 posts and you'll have your answer...

Woody.

 

[SQLBill]

Woody,

I wasn't making a comment about either of your solutions. But if the original poster is not working with SQL Server, stored procedures won't work. Some of the datatypes in both of yours (you and DotNetGnat) solutions are only SQL Server datatypes and won't work with Oracle or MS Access databases. The way the variables are used won't work if it's not a SQL Server database.

That's why I was trying to get clarification from the OP. Some people think this is a SQL language forum when it's not.

I just helped a poster on another thread get to the right forum. People were helping the poster with code and I realized the poster was using Oracle and none of the posted suggestions would work.

So, I wasn't making any comment about your solutions...just trying to let the poster know that they might fail if he is using something besides SQL Server as a backend.

-SQLBill

Posting advice: FAQ481-4875

 

[Warlocks]

SQLBill,

I have just realised the confusion.
When I said Bill in my last post, I was referring to BDakis as he signed his post as "Bill".

I concur with your attention to detail in the fact that even the database has not been clarified but the assumption is that it is SQLServer.

Hope this clears things up!!

Woody.