February 22, 2008 at 5:31 am
I have three tables:users,users_roles,role
The records in these tables are as follows.
users_roles
**************
uidrid
13
983
1063
1233
role
****
ridname
1anonymous user
2authenticated
3admin
users
*******
uidnamemail
98bb@yahoo.com
106cc@sify.com
123dd@yahoo.co.in
Now I want to list the names of authenticated users alone from these tables.
Can anyone help me in writing the query for this
Thanks in advance
February 22, 2008 at 6:25 am
What have you tried so far?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2008 at 6:31 am
I have wrote query for listing out the admin users
But I dont know how to write query for listing the authenticated users.
February 22, 2008 at 6:45 am
it's same query then, just different role_id in it.
Piotr
...and your only reply is slàinte mhath
February 22, 2008 at 6:47 am
OK. Well, have you tried using a JOIN to put the data from the three tables together?
I could simply write the query for you, but this looks a bit like homework. Better you learn to fish than I hand you one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2008 at 9:16 pm
SELECT U.uid,U.name,U.mail,R.name
FROM users U
INNER JOIN users_roles Ur
ON U.uid=Ur.uid
INNER JOIN role R
ON Ur.rid=R.rid;
This query displays the list of admin users.
I tried the following query using WHERE condition for listing out authenticated users.
SELECT U.uid,U.name,U.mail,R.name
FROM users U
INNER JOIN users_roles Ur
ON U.uid=Ur.uid
INNER JOIN role R
ON Ur.rid=R.rid
WHERE R.rid=2;
But this query does not display any records.
Could anyone help?
February 24, 2008 at 9:56 pm
SELECT U.uid,U.name,U.mail,R.name
FROM users U
INNER JOIN users_roles Ur
ON U.uid=Ur.uid
INNER JOIN role R
ON Ur.rid=R.rid
WHERE R.rid=2;
But this query does not display any records.
The above query should not display any records. Simply because there is no record for rid=2 in your sample table users_roles
users_roles
**************
uid rid
1 3
98 3
106 3
123 3
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
February 24, 2008 at 10:00 pm
I want to display the list of users except the admin users from the users table.
So what query will help to fetch these records.
February 24, 2008 at 10:34 pm
SELECT U.uid,U.name,U.mail,R.name
FROM users U
INNER JOIN users_roles Ur
ON U.uid=Ur.uid
INNER JOIN role R
ON Ur.rid=R.rid
WHERE R.rid Not = 3;
Try this. This will also not display any data as you do not have any data other than Admin
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply