CODEWITHSUNDEEP
mysqlsql
Saravanakumar N
Saravanakumar N

Reputation: 23

MySql-How to set two alias name to single column

select event_id,
       IF(event_type!='C',e.event_title ,'') event_title,
       IF(event_type='C', e.event_title ,'') event_subtitle
from event e where log_id='%s'

this query results only for "event_title", and show empty result for "event_subtitle", even though i have data for the result "event_subtitle"

Upvotes: 1

Views: 219

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I wonder if you need aggregation:

select event_id,
       max(case when event_type <> 'C' then e.event_title end) as event_title,
       max(case when event_type = 'C' then e.event_title end) as event_subtitle
from event e
where log_id = '%s'
group by event_id;

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

check this:

select event_id,
       case when event_type<>'C' then e.event_title  end  as  event_title,
       case when event_type='C'then e.event_title end as  event_subtitle
from event where log_id='%s';

fro example:

SELECT ename ,
case when job='SALESMAN' then  ename end as  salesman_ename,
case when job<>'SALESMAN' then  ename end as not_salesman_ename
FROM scott.emp;

SMITH       SMITH
ALLEN   ALLEN   
WARD    WARD    
JONES       JONES
MARTIN  MARTIN  
BLAKE       BLAKE
CLARK       CLARK
SCOTT       SCOTT
KING        KING
TURNER  TURNER  
ADAMS       ADAMS
JAMES       JAMES
FORD        FORD
MILLER      MILLER
H       H

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17615

The code 'works' for example

drop table if exists event;
create table event(event_id int, event_type varchar(1), event_title varchar(10));

insert into event values
(1,'c','aaa'),(2,'x','xxx');

select event_id,
       IF(event_type!='C',e.event_title ,'') event_title,
       IF(event_type='C', e.event_title ,'') event_subtitle
from event e;

+----------+-------------+----------------+
| event_id | event_title | event_subtitle |
+----------+-------------+----------------+
|        1 |             | aaa            |
|        2 | xxx         |                |
+----------+-------------+----------------+
2 rows in set (0.00 sec)

If your data model does not look like this or the output based on my sample data is not what you expect please add sample data and desired output to the question as text.

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

You are write event_title in both condition but as per your requirement I know that you want IF(event_type='C', e.event_subtitle ,'') event_subtitle

Change From 

IF(event_type!='C',e.event_title ,'') event_title,
IF(event_type='C', e.event_title ,'') event_subtitle

To

IF(event_type!='C',e.event_title ,'') event_title,
IF(event_type='C', e.event_subtitle ,'') event_subtitle
                      ^^^^^^^^^^^

Upvotes: 0

Related Questions