help with join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with join

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-22 : 15:09:32

I hope this is what you guys mean

Hi,

I'm working on creating access levels to the website I am working on.
I have created a new table named "tblMemberShips" with the following columns.


purchasedFor purchasedBy  dateStart dateEnd membershiptype

500          500         2001-01-01   2005-05-05 10 
500          500         2001-11-11   2006-05-05 15
500          500         2001-01-01   2007-05-05 20
500          500         2002-01-01   2008-05-05 20
500          500         2002-01-01   2009-05-05  5

When the user logs on, I join their regular info onto this table and determine what access level to set them to.
The problem I am having is the amount of rows being brought back.

I only need one row to be brought back. That would be the row with the with the greatest access level
then ordered by greatest expiry after that.

The reason for this is because if the person has 2 membership levels.
For example they have 1 year at access level 10, but for 1 month they are a "20".
I want to make sure the "20" is brought back and not the 10.

Whats the best way to approach this ?

Thanks alot for any assitance

mike123


CREATE PROCEDURE DBO.select_logon 
	(
	 @nameOnline varchar(15),
	 @password varchar(20)
	)
AS SET NOCOUNT ON

IF EXISTS 
	(SELECT tblUserDetails.userID FROM tblUserDetails 
	INNER JOIN tblMemberships ON tblMemberships.purchasedFor = 
tblUserDetails.userID 

	INNER JOIN (SELECT purchasedFor, Max(dateEnd) as dateEnd FROM 
tblMemberships GROUP BY purchasedFor) M ON M.purchasedFor = 
tblMemberships.purchasedFor AND M.dateEnd = tblMemberships.dateEnd 
WHERE nameOnline = @nameOnline and password = @password)

	SELECT tblUserDetails.userID, nameOnline, active, guid, max
(m.dateEnd, m.membershipType FROM tblUserDetails INNER JOIN 
tblMemberships ON tblMemberships.purchasedFor = tblUserDetails.userID 

INNER JOIN (SELECT purchasedFor, Max(dateEnd) as dateEnd, 
membershipType FROM tblMemberships GROUP BY purchasedFor, 
membershipType) M ON M.purchasedFor
 = tblMemberships.purchasedFor AND 
M.dateEnd = tblMemberships.dateEnd WHERE 
nameOnline = @nameOnline and 
password = @password

ELSE

	SELECT tblUserDetails.userID, nameOnline, active, guid FROM 
tblUserDetails WHERE nameOnline = @nameOnline and password = @password



GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 15:25:28

Please help us out with some New-Lines and some formatting -- it is a pain when the thread goes 2 screens wide.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 15:28:38

no -- code tags are fine -- please edit your first post and just hit enter a few times to format it better. How does this thread display on your screen? do you see how it is being stretched?

makes sense?

- Jeff

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-22 : 15:28:49

that should be better.. i thought the code tags would help but your right it does make it a bitch

thx ;)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 15:32:22

Keep the code tags, but put some new lines in the queries so that they don't scroll off the screen.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-07-22 : 15:47:45

hope thats better

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 15:56:12

yes, a little better. thanks. this is not the solution (yet), but here is your code formatted a little more properly:


CREATE PROCEDURE DBO.select_logon 
	(
	 @nameOnline varchar(15),
	 @password varchar(20)
	)
AS SET NOCOUNT ON

IF EXISTS 
	(SELECT 
              tblUserDetails.userID 
         FROM 
              tblUserDetails 
	INNER JOIN 
              tblMemberships 
        ON 
              tblMemberships.purchasedFor = tblUserDetails.userID 
	INNER JOIN 
             (SELECT 
                  purchasedFor, Max(dateEnd) as dateEnd 
              FROM 
                  tblMemberships 
              GROUP BY 
                  purchasedFor) M 
        ON 
              M.purchasedFor = tblMemberships.purchasedFor AND 
              M.dateEnd = tblMemberships.dateEnd 
        WHERE 
              nameOnline = @nameOnline and password = @password)

   SELECT 
           tblUserDetails.userID, nameOnline, active, guid, 
           max(m.dateEnd), m.membershipType 
   FROM 
           tblUserDetails 
   INNER JOIN 
           tblMemberships 
   ON 
           tblMemberships.purchasedFor = tblUserDetails.userID 
   INNER JOIN 
          (SELECT  
                  purchasedFor, Max(dateEnd) as dateEnd, 
                  membershipType 
           FROM 
                  tblMemberships 
           GROUP BY 
                  purchasedFor, membershipType) M 
   ON 
        M.purchasedFor = tblMemberships.purchasedFor AND 
        M.dateEnd = tblMemberships.dateEnd 
   WHERE 
        nameOnline = @nameOnline and 
        password = @password

ELSE

	SELECT  
              tblUserDetails.userID, nameOnline, active, guid 
        FROM 
              tblUserDetails 
        WHERE 
              nameOnline = @nameOnline and password = @password

doesn't that look a little easier to intepret?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 16:01:48

not sure, but i think all you need is this:


  SELECT Top 1
           u.userID, u.nameOnline, u.active, u.guid, 
           m.dateEnd, m.membershipType 
   FROM 
           tblUserDetails u
   LEFT OUTER JOIN
           tblMemberships n
   ON 
           m.purchasedFor = u.userID AND
           getDate() between m.DateBegin and m.DateEnd 
   WHERE 
        u.nameOnline = @nameOnline and 
        u.password = @password
   ORDER BY 
        m.MembershipType DESC, m.DateEnd DESC

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 17:08:47

I've only glanced at this, but if I've got the right end of the stick a user gets a "level" and that "level" allows them to do certain stuff.

If I've got that right can I urge you to consider "roles" rather than "levels".

We did the levels thing, because it was easier than "roles" and almost instantly hated it and wished for a roles-based solution.

We immediately had clients saying "But I need my subscribed members who pay the "Delux subscription" to be able to do this, but the ones on the "one-shot" subscription get to do it differently".

What we wound up with was a comma delimited list of roles (in the session object) and we just SPILT that and compare against the Role required for a given web page.

So for example a web page has a required permission of DELUX,ONE-SHOT and my account has roles of EVERYONE,DELUX,ADMIN and all I have to do is establish that at least one of the Web Page permissions is contained within my list of roles.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources