June 9, 2003 at 1:34 pm
Hi everybody,
I am a real rookie when it comes to writing sql commands so I am hoping someone will be able to help me out. What I am trying to do would best be compared to how a typical dating website works. I have two tables and customer types we will call guys and dolls. Both the guys and dolls can sign up for a free or paid membership. If a free membership is chosen, then the members will be able to read the details on the person they are inquiring about but will not be able to get their contact information unless they or the member they are inquiring about has paid for their membership. So in other words two members " a guy and a doll" would not be able to contact each other unless one of them has a paid membership.
On my results page after a guy has read the details on a doll he clicks on a link to bring him to the contact information page. I need the sql statement which will check first to see if the guy is a paid member and if not the statement then needs to check if the doll is a paid member. If that comes up negative then the guy will be given the opportunity to upgrade his membership so he will be able to contact this doll.
Presently on my contact information page I have two recordsets.
The first recordset coming from the guys table provides the userID and access level for the guy.
The second recordset coming from the dolls table provides the contact information (a phone number and email address)plus a userID and access level for the doll.
For the guy to access the contact information his access level must be 5 (free membership access level is 3)If he doesn't have a 5 then the babysitter must have a access level of 4 (free membership access level is 2)to be able to get her contact info.
Guys table name is > GUYS
Field names are gUserID and gAccessLVL
Dolls table name is > DOLLS
Field names are dUserID and dAccessLVL and dEmail and dPhone
Any help will be very much appreciated,
Thanks
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
June 9, 2003 at 1:48 pm
CREATE PROCEDURE dbo.HasPaid
(
@GuyID INT NOT NULL
, @DollID INT NOT NULL
, @GuyPaid BIT NOT NULL OUTPUT
, @DollPaid BIT NOT NULL OUTPUT
)
AS
-- First, guy
SELECT @GuyPaid = CASE WHEN gAccessLvl > 4 THEN 1 ELSE 0 END
FROM Guy
WHERE gUserID = @GuyID
-- Next gal
SELECT @DollPaid = CASE WHEN dAccessLvl > 4 THEN 1 ELSE 0 END
FROM Doll
WHERE dUserID = @DollID
GO
-- Execute the procedure like so:
DECLARE @GID INT, @DID INT, @DidGuyPay BIT, @DidDollPay BIT
SET @GID = 234234 -- Som ID from your DB
SET @DID = 547547 -- Some Doll ID
EXEC HasPaid @GID, @DID, @DidGuyPay OUTPUT, @DidDollPay OUTPUT
HTH,
Jay
June 9, 2003 at 7:51 pm
Jay thanks for the reply,
I know very little about stored procedures but I'll try and figure it out and put it to work. Is it possible to get the results I am looking for with a sql statement or must I use a procedure which I understand is prefered.
Thanks again,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
June 10, 2003 at 5:34 am
quote:
...Is it possible to get the results I am looking for with a sql statement or must I use a procedure....
Of course, I always encourage the use of procedures...that said, what makes this situation unique is that you are storing guy and doll information in 2 separate tables, hence my leaning towards a procedure. In straight sql, you would have to UNION the two tables togather to get a single recordset with two records back, then in your ASP code, loop through the 2 records:
SELECT CASE WHEN gAccessLvl > 4 THEN 1 ELSE 0 END AS HasPaid
FROM Guy
WHERE gUserID = @GuyID
UNION ALL
SELECT CASE WHEN dAccessLvl > 4 THEN 1 ELSE 0 END AS HasPaid
FROM Doll
WHERE dUserID = @DollID
Of course, you would replace the parameters (the @GuyID and @DollID with the values from your ASP page)
On a further note, I would guess that the Guy and Doll tables are fairly similar if not identical. If so, it would make your life a lot easier if you combined the table into one:
CREATE TABLE Participant
(
ParticipantID INT NOT NULL IDENTITY(1,1)
, Gender CHAR(1) NOT NULL
--, More Fields here...
, AccessLevel TINYINT NOT NULL DEFAULT(3)
)
This way, you can forget the complexity shown in your original design and do something like this:
SELECT SUM(AccessLevel)
FROM Participant
WHERE ParticipantID = @GuyID
OR ParticipantID = @DollID
You know that if the result returned from this query is greater than 6 (since the guest access level is 3), one or the other has full access to the site...
Hope this heads you in the right direction,
Good luck,
Jay
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply