How to fix this Query

Posted by Jayakumars under .NET Framework on 11/29/2017 | Points: 10 | Views : 519 | Status : [Member] [MVP] | Replies : 7
hi

create table f1
(
id int primary key identity(1,1),
Cname nvarchar(20),
CountryID int,
RoleId int
)

insert into f1 values('AA',1,2)
insert into f1 values('BB',1,2)
insert into f1 values('CC',1,1)

insert into f1 values('AA',2,3)
insert into f1 values('BB',2,1)
insert into f1 values('CC',2,4)

insert into f1 values('AA',3,4)
insert into f1 values('BB',3,6)
insert into f1 values('CC',3,1)

insert into f1 values('AA',4,2)




select * from f1 --where RoleId=2

-- I need the data roleid 2 then which country not assigned 2 that will also come

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Dharmraj on: 11/29/2017 [Member] Starter | Points: 25

Up
0
Down
Please explain this line in detail... I am not getting question

I need the data roleid 2 then which country not assigned 2 that will also come

Dharmraj Thakur

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neerajprasadsharma on: 11/29/2017 [Member] Bronze | Points: 25

Up
0
Down
Hi jayaKumarks, if i get your question right i guess you are looking for this:



SELECT * FROM F1 WHERE (RoleId=2 OR CountryID!=2)





Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 11/29/2017 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Thanks
Dharma and neeraj

My Issue is In My Database 50 Company is there

Countryid,name ,roleid have that


But here every company they set roleid 2

some company not set roleid2

so i need which company set roleid 2 it will comes then not assign roleid 2 also come for remaining company




Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandyamanav on: 11/30/2017 [Member] Starter | Points: 25

Up
0
Down
Hello

I think u r not sure about your question

You shuld try :

select * from f1 Where roleid =2 and countryid=2


and if you want only country id than you can specify separately

Thanks

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kgovindarao523-21772 on: 12/11/2017 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Query

SELECT 'Data With Role Id 2' AS Comment, CName FROM f1 WHERE RoleId = 2

UNION ALL
SELECT 'Without Role Id 2' AS Comment, CName FROM f1 WHERE RoleId != 2


Thank you,
Govind

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anujarishimehta on: 12/14/2017 [Member] Starter | Points: 25

Up
0
Down
Hope this helps.

select 'Not assigned roleid' as Status, * from f1
where countryid not in (
select countryid from f1 where roleid = 2)

select 'Assigned Roleid' as Status, * from f1 where roleid = 2

Thanks & Regards,

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 12/19/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@JayaKumar, tricky question. You want the records for the countries with RoleId = 2 should appear first followed by Countries which has not been assign Role Id = 2. A simple sorting will do as under

select * from f1 
order by IIF(f1.RoleId =2,1,2)


Result
----------

id	Cname	CountryID	RoleId
1 AA 1 2
2 BB 1 2
10 AA 4 2
3 CC 1 1
4 AA 2 3
5 BB 2 1
6 CC 2 4
7 AA 3 4
8 BB 3 6
9 CC 3 1


Now, I will further show you something interesting. Suppose we want to present the records with RoleId's as 2,1,3,4,5,6 etc., then the sorting logic should be done as under

select * from f1 
order by
IIF(f1.RoleId =2,1,IIF(f1.RoleId =1,2,IIF(f1.RoleId =3,3,IIF(f1.RoleId =4,4,IIF(f1.RoleId =5,5,IIF(f1.RoleId =6,6,7))))))


Result
------


id	Cname	CountryID	RoleId
1 AA 1 2
2 BB 1 2
10 AA 4 2
9 CC 3 1
3 CC 1 1
5 BB 2 1
4 AA 2 3
6 CC 2 4
7 AA 3 4
8 BB 3 6


Hope this helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response