CODEWITHSUNDEEP
mysqlsqlconditional-statements
ps4one
ps4one

Reputation: 57

Different select statement for different condition

This is my current mysql query.

Current table query in sqlfiddle http://sqlfiddle.com/#!2/6e0420

If I select * from table, I get the below

RN  PC  PC1 GRP E_ID
111 A1  A1  175 100
112 A1  A2  100 90
113 B1  B3  101 90
114 B1  B1  100 90
115 B1  B5  100 90
116 C1  C1  100 90

But I am trying to get this output 

RN  PC  PC1 GRP E_ID
111 A1  A1  175 100
112 A1  A2  
113 B1  B3  
114 B1  B1  100 90
115 B1  B5  
116 C1  C1  100 90

So the condition is if pc=pc1 then the grp and e_id should be shown, otherwise if pc!=pc1 then the grp and e_id should be empty

 Current query 
        select *
        from table1 
        where rn in(select rn from table1 group by rn having count(*)=1)
        AND (pc = pc1)


 Solution
       select rn, pc, pc1, 
       case when pc = pc1 then grp else null end as grp,
       case when pc = pc1 then e_id else null end as e_id
       from table1
       where rn in(select rn from table1 group by rn having count(*)=1)

QN2: The above solution i have added count = 1 because i have another sql query if the count is > 1. How do i combine both the query, basically its split by count =1 and count >1 Below is that sql query

select *
from table1 
where rn in(select rn from table1 group by rn having count(*)>1)
AND (pc = pc1)
AND grp in (select max(grp) from table1)
AND e_id in( select min(e_id) from table1)

The conditions for count>1 shld not affect count = 1 results.

I found the solution already, which is to union them. Thanks for the first part.

Upvotes: 0

Views: 84

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

You can just use a case when, and display null (or something else) when pc <> pc1

select rn, pc, pc1, 
       case when pc = pc1 then grp else null end as grp,
       case when pc = pc1 then e_id else null end as e_id
from table1

which can be simplified (for null replacement)

select rn, pc, pc1, 
       case when pc = pc1 then grp end as grp,
       case when pc = pc1 then e_id  end as e_id
from table1

Upvotes: 3

Related Questions