CODEWITHSUNDEEP
mysqlcaseconditional-statements
Esteban Cazorla
Esteban Cazorla

Reputation: 151

MySQL column with conditional value

I have this basic SQL sentence:

SELECT p.s1,pr.s1 FROM `prop` p LEFT OUTER JOIN prom pr ON pr.id = p.prom

My intention is to have a column with the value of pr.s1 if it's not null, and p.s1 in other case. It's very simple, but i don't know how to do it correctly.

Any idea will be very appreciated, thanks.

Upvotes: 0

Views: 34

Answers (4)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

You can use IFNULL:

SELECT IFNULL(pr.s1, p.s1) AS s1
FROM `prop` p
LEFT OUTER JOIN prom pr
    ON pr.id = p.prom

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

To round things out, you could also use COALESCE:

SELECT COALESCE(pr.s1, p.s1)
FROM prop p
LEFT OUTER JOIN prom pr
    ON pr.id = p.prom

One nice thing about using COALESCE() over IFNULL() is that the former can take more than two parameters. So if, for example, you had two backup columns in case of a NULL value you could do:

SELECT COALESCE(pr.s1, p.s1, p.someOtherCol)

Upvotes: 2

sagi
sagi

Reputation: 40481

You can use COALESCE() :

SELECT p.s1,COALESCE(pr.s1,p.s1) 
FROM `prop` p 
LEFT OUTER JOIN prom pr
 ON pr.id = p.prom

Although you may have two identical columns, so I think you meant :

SELECT COALESCE(pr.s1,p.s1) as s1
FROM ...

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You can use ifnull 

 SELECT p.s1, ifnull(pr.s1, p.s1) 
 FROM `prop` p 
 LEFT OUTER JOIN prom pr ON pr.id = p.prom

Upvotes: 1

Related Questions