How to fix this Query

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

0	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

0	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

0	@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

@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

Latest Posts