Declare @IsExistRoleAR As bit Declare @IsExistRoleEN As bit Declare @IsExist AS bit set @IsExistRoleAR=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists FROM Roles WHERE RoleDescAR='Arabic Name') set @IsExistRoleEN=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists FROM Roles WHERE RoleDescEN='English Name') set @IsExist=(@IsExistRoleAR | @IsExistRoleEN) select @IsExist
You don't really need a CASE statement here, unless I've missed something. (Always possible.)
select count(RoleID) from roles where RoleDescEN = 'EnglishName' or RoleDescAR = 'ArabicName';
If you're trying to identify persons who have those roles, you might be looking for something along these lines.
create table roles ( RoleID integer primary key, RoleDescEN varchar(30) , RoledescAR varchar(30) ); insert into roles values (1, 'EnglishName', null), (2, null, 'ArabicName'), (3, 'EnglishName', null), (4, 'EnglishName', null), (5, null, 'ArabicName'), (6, null, 'ArabicName'), (7, null, 'OtherName'); create table persons ( person_id integer primary key, person_name varchar(35) not null, person_role integer references roles (RoleID) ); insert into persons values (1, 'Obama, Barak', 1), (2, 'Obama, Michelle', 2), (3, 'Biden, Joe', 7);
I would not be surprised if my tables don't match yours. In your question, always include CREATE TABLE statements and INSERT statements to get the best answers.
select * from persons p inner join roles r on r.RoleID = p.person_role where RoleDescEN = 'EnglishName' or RoleDescAR = 'ArabicName';
Since you haven't accepted any of the other answers, I'm going to take a guess that perhaps you want more information back:
SELECT CASE WHEN EXISTS (SELECT * from Roles Where RoleDescAR='Arabic Name') then 'AR' WHEN EXISTS (SELECT * from Roles Where RoleDescEN='English Name') then 'EN' ELSE 'none' END As whichExists
Select Case When exists (Select * From Roles WHERE RoleDescAR='Arabic Name' Or RoleDescEN='English Name') Then 1 Else 0 End
If you want to know the names of the people who meet this criteria, that's a completely different question, then you need to provide the schema for the table that has one row per each person, and the roleId, and then, (assuming that table is named
Select Name From Person p Where exists (Select * From Roles Where RoleId = p.RoleId And RoleDescAR='Arabic Name' Or RoleDescEN='English Name')