How can I ensure that ALL users have the ability to be authenticated?

Question

How can I ensure that ALL users have the ability to be authenticated?

simflex-897410

Hall of Fame

Points: 3881
More actions
March 14, 2014 at 8:48 pm

#289425

Jeff or anyone, can you please advise me on how to post my data?
These names are actual names of popular people and I can't post their information here.
Our site has 5 pages. Only one group of users with access level 3 are allowed to view all sites.
Those without access level 3 have less priviledges.
table1 has a list of all users with access level 3
The structure is thus:
table1
table1Id
access_
level userid
Then there is table2 with rest of users
table2
table2Id
userId
username
password
All users in table1 also belong to table2. That's why they are able to see all pages.
userId is the relationship between table1 and table2.
What I would like to accomplish with this query below is ensure that ALL users, both on table1 and table2 are accounted for.
In other words, they all have the ability to login in successfully.
Part two of my task is to ensure that only users with access level 3 get to see all pages while those without it, see limited pages.
This part is not part of this question.
My question is, how do I ensure that all users have the ability to successfully log in as long as their credentials can be authenticated?
Below is code and thank you in advance for your help.
select username,
password,
isnull(access_level,0) access_level
from table2 e left join table1 m on e.userid = m.username
where username is not null
This query is producing only 167 records. No users with access_level 3 are listed.
There are a total of 589 records with access_level 3 and 7,209 in all.
What's wrong with my query?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

simflex-897410 (3/14/2014)
Jeff or anyone, can you please advise me on how to post my data?
These names are actual names of popular people and I can't post their information here.
Our site has 5 pages. Only one group of users with access level 3 are allowed to view all sites.
Those without access level 3 have less priviledges.
table1 has a list of all users with access level 3
The structure is thus:
table1
table1Id
access_
level userid
Then there is table2 with rest of users
table2
table2Id
userId
username
password
All users in table1 also belong to table2. That's why they are able to see all pages.
userId is the relationship between table1 and table2.
What I would like to accomplish with this query below is ensure that ALL users, both on table1 and table2 are accounted for.
In other words, they all have the ability to login in successfully.
Part two of my task is to ensure that only users with access level 3 get to see all pages while those without it, see limited pages.
This part is not part of this question.
My question is, how do I ensure that all users have the ability to successfully log in as long as their credentials can be authenticated?
Below is code and thank you in advance for your help.
select username,
password,
isnull(access_level,0) access_level
from table2 e left join table1 m on e.userid = m.username
where username is not null
This query is producing only 167 records. No users with access_level 3 are listed.
There are a total of 589 records with access_level 3 and 7,209 in all.
What's wrong with my query?

Are all users in table1 also in table2?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

select username,

password,

isnull(access_level,0) access_level

from table2 e left join table1 m on e.userid = m.username

where username is not null

Also, is the ON clause in the join correct? Do you want to join e.userid to m.username? Or do you want to join it to m.level_userid?

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 3

Hi Lynn,

Thank you so much for your responses.

I must say though that I am very confused with your questions.

You asked if table1 is related to table2 by userid?

I did say so in my original post.

But I don't understand your second post

Your query is exactly same as mine and I don't understand the proceeding question.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

simflex-897410 (3/14/2014)
Hi Lynn,
Thank you so much for your responses.
I must say though that I am very confused with your questions.
You asked if table1 is related to table2 by userid?
I did say so in my original post.
But I don't understand your second post
Your query is exactly same as mine and I don't understand the proceeding question.

Okay. If my userid is listed in table1, is my userid also listed in table2?

Take a close look at your query, it is also copied in my post where I ask you to verify the ON clause of the FROM clause. It doesn't look correct to me.