CODEWITHSUNDEEP
sqloracle-databaseunion
DuMont
DuMont

Reputation: 55

Union with Case?

First of all, thank you for your time helping me!

I have the following (example) table:

UNDEAD

User     UserGroup     IsAdmin
_____________________________
foo      zombie        N
bar      skeletton     N
blub     vampire       Y

now I want something like this: 

select UserGroup 
from UNDEAD
where User = blub

--> Of course, it is "vampire"

Now My Question: An Admin should get all groups, so the results for the different users are:

How can I reach this goal?

Upvotes: 1

Views: 4263

Answers (5)

Rohan
Rohan

Reputation: 2030

Hope this helps SELECT * FROM UNDEAD S WHERE (CASE WHEN 'blub' = (SELECT DISTINCT S1.USER FROM UNDEAD S1 WHERE S1.ISADMIN = 'Y') THEN 'TRUE' END) = 'TRUE' UNION SELECT * FROM UNDEAD S WHERE S.USER = 'blub'

You may change the values(blub, foo, bar) in code to check. It gives all rows for ADMIN Y, and specific for N

Upvotes: 0

Alex
Alex

Reputation: 21766

You can use a UNION to achieve this. First, you select all non-admin users and their associated groups. Secondly you need to perform a self join that returns every group for each admin user and UNION the results:

SELECT UserName ,
       UserGroup
FROM   undead
WHERE  IsAdmin = 'N'
UNION
SELECT u1.UserName,u2.UserGroup 
FROM undead u1, undead u2
WHERE u1.IsAdmin='Y'

Please note that I changed the name of the User column as it is a reserved keyword. See below the setup script I have created.

CREATE TABLE undead(  
  UserName VARCHAR2(100),
  UserGroup VARCHAR2(100),
  IsAdmin VARCHAR2(100));

INSERT INTO undead(UserName, UserGroup, IsAdmin) VALUES('foo','zombie','N');

INSERT INTO undead(UserName, UserGroup, IsAdmin) VALUES('bar','skeleton','N');

INSERT INTO undead(UserName, UserGroup, IsAdmin) VALUES('blub','vampire','Y');

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can even use a dynamic sql query.

Query

set @query = null;
set @user = 'foo';
select distinct case when `User` = @user and `IsAdmin` = 'Y'
  then 'select distinct `UserGroup` from `UNDEAD`;'
  else concat('select `UserGroup` from `UNDEAD` where `User` = ''', @user, ''';') end
into @query
from `UNDEAD`
where `User` = @user;

prepare stmt from @query;
execute  stmt;
deallocate prepare stmt;

Here based on the @user value the sql query will be executed. It will check for IsAdmin. If Y then all unique UserGroup else UserGroup for that particular user.

SQL Fiddle demo

Upvotes: 0

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

A union is not necessary for this, I think.

select distinct UserGroup 
from undead 
where [User] = 'bulb' or (select IsAdmin from undead where [User] = 'bulb') = 'Y'

Herzlich Willkommen in StackOverflow aus Athen!

Upvotes: 0

Serge
Serge

Reputation: 4036

SELECT UserGroup
FROM UNDEAD
WHERE User = blub

UNION

SELECT DISTINCT UserGroup
FROM UNDEAD
WHERE 
(
    SELECT IsAdmin
    FROM UNDEAD
    WHERE User = blub
) = 'Y'

Upvotes: 0

Related Questions