0
\$\begingroup\$
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
\$\endgroup\$
0
8
\$\begingroup\$

You could just use OR:

  SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
  FROM Roles
  WHERE RoleDescAR='Arabic Name' OR RoleDescEN='English Name'
\$\endgroup\$
2
  • \$\begingroup\$ I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that \$\endgroup\$
    – Mohammed Qamhawi
    Jun 10 '13 at 15:43
  • \$\begingroup\$ Add columns to select statement like: SELECT RoleDescAR, RoleDescEN, CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists \$\endgroup\$
    – Garath
    Jun 10 '13 at 15:49
0
\$\begingroup\$

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';
\$\endgroup\$
2
  • \$\begingroup\$ I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that \$\endgroup\$
    – Mohammed Qamhawi
    Jun 10 '13 at 15:45
  • \$\begingroup\$ I think you mean, "I want to know the names of everyone who has either the role 'Arabic Name' or the role 'English name'." (I could easily be wrong about that.) The code you posted makes no attempt to do that, and it doesn't suggest that there might be other tables involved. Edit your question, and paste the CREATE TABLE statements from the relevant tables. \$\endgroup\$ Jun 10 '13 at 15:48
0
\$\begingroup\$

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
\$\endgroup\$
0
\$\begingroup\$
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 Person) Try:

  Select Name
  From Person p
  Where exists
   (Select * From Roles
    Where RoleId = p.RoleId
        And RoleDescAR='Arabic Name' Or 
            RoleDescEN='English Name')
\$\endgroup\$
1
  • \$\begingroup\$ I want to know who is who was exists at table Roles "Arabic Field" Or English Field? I don't know how to explain that \$\endgroup\$
    – Mohammed Qamhawi
    Jun 10 '13 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy