Pls help to solve this query

Question

Pls help to solve this query

Prakash-485822

SSC Eights!

Points: 982
More actions
January 30, 2008 at 6:11 am

#186081

Dear All,
I have a two table Users && Roles
Roles contains following columns
Roleid, Rolename,InboxEnabled
Users contains
Userid,Region,Branchid,Roles
The Data in the Roles Table is
Admin -Admin -y
Enduser-EndUser-n
Dev -Developer-y
Network-Network-y
The data in the users table is
Ravi-North-Delhi-Admin,Dev,Network,
Raju-North-Delhi-Dev,Enduser,
select Roleid from Roles where Inboxenabled ='y' will return me Admin and Dev roles
when i pass this query as a condition to users table i have to get output as Ravi
Anyone please help me to solve this query
i

Viewing 11 posts - 1 through 10 (of 10 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Please don't cross-post. Especially don't post things in the 2008 forums that has no relation to 2008.

"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

raja_saminathan SSCommitted Points: 1607 More actions · Answer 2

Hi Prakash,

Have you analysed your Condition before posting ,it is not possible to select only ravi because you have given Inboxenabled for Admin,Dev as Y,

How can the query select based on this condition Ravi only

First analyse and then post it and it will be better if you post as like below format

Here is my result

create table Roles(Roleid varchar(20), RoleName Varchar(20),InboxEnabled Varchar(20))

create Table Users(Userid varchar(20), Region Varchar(20), Branchid Varchar(20),Roles Varchar(20))

insert into users

select 'ravi','northDelhi','Admin','Network' union

select 'raju','northDelhi','dev','enduser'

insert into Roles

select 'ADmin','admin','y' union

select 'Enduser','EndUser','n' union

select 'Dev','Developer','y' union

select 'Network','Network','y'

select userid from users where Branchid in (select Roleid from Roles where Inboxenabled ='y')

rajesh

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

I think Grant is Correct..

Only SQl Server 2008 Post should be placed here..

so we can able to analyzes more on Sql Server 2008..

before going to implements sql 2008 in our developments server..

Cheers!

Sandy.

--

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 4

select userid from users where Branchid in (select Roleid from Roles where Inboxenabled ='y')

- Pradyothana http://www.msqlserver.com

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Grant Fritchey (1/30/2008)
Please don't cross-post. Especially don't post things in the 2008 forums that has no relation to 2008.

Heh... well that worked well... wanna borrow some porkchops? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Jeff Moden (7/21/2008)
Grant Fritchey (1/30/2008)
Please don't cross-post. Especially don't post things in the 2008 forums that has no relation to 2008.
Heh... well that worked well... wanna borrow some porkchops? 😛

Hmmm, seems someone decided to post an answer to a post 6 months old. And, to a post that was a cross-post and noted as cross-posted.

Yeah - I think Grant needs to borrow some porkchops...;)

But, can you save some for me - I have a strong feeling I am going to need them real soon :w00t:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

So what do I do with these pork chops? Sharpen the bones & throw 'em like a boomarang?

"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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Now that's an interesting use... I normally just use an inner tube sling shot with a foot wide maw on it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

Oh, I've got one of those for the dog... it'll work.

"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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 10

IN_Sandeep

SSCertifiable

Points: 6259

July 23, 2008 at 11:54 pm

#847534

😉

--