Hi all,
I have a table name Orders with fields "municipality, zone, region". I want to list/select according to this condition: Show municipality If valued, Else show zone if valued, else show region...
How do i write a SQL statement with IF...ELSE... conditions.
best practice would be to use a active records database pattern witch is object orientated. But to answer you question you would do all your checking of what to insert before you insert the data.
SQL can't do if and else if statements but php can so seperate the data you need to first then insert it into the right table.
jgetner;10955491 wrote:best practice would be to use a active records database pattern witch is object orientated. But to answer you question you would do all your checking of what to insert before you insert the data.
SQL can't do if and else if statements but php can so seperate the data you need to first then insert it into the right table.
Im not inserting, im selecting, basically these values are inserted already, I just want to list them
jgetner wrote:SQL can't do if and else if statements
Oi, says who??
SELECT IFNULL(municipality, IFNULL(zone, region)) AS munizongion, ...This will, of course, give you the value you desire (in a nifty column alias, if you ask me 
) ... though it won't let you determine which value you got. Best you can say is that either the municipality, zone, or region had a value of "foo".
If that suits your application's needs, great! If not, best route is to retrieve all three values and do a bit of processing in PHP.
Also note that I assumed by a column not being "valued", you meant that it had a NULL value (not to be confused with an empty string or anything of that nature).
bradgrafelman;10955500 wrote:Also note that I assumed by a column not being "valued", you meant that it had a NULL value (not to be confused with an empty string or anything of that nature).
If municipality is not null then return its value, else return zone's value if not null, else just return region
Tonata;10955504 wrote:If municipality is not null then return its value, else return zone's value if not null, else just return region
Right, that's what my example query snippet above would do.
SELECT COALESCE(municipality,zone, region) AS munizongion, announceType
FROM annoucements;
CASE WHEN might be an option: http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
Something like this i think:
SELECT (CASE `municipality` WHEN NOT NULL THEN `municipality` ELSE
(CASE `zone` WHEN NOT NULL THEN `zone` ELSE `region` END)
END) AS munizongion
FROM OrdersTonata;10955511 wrote:SELECT COALESCE(municipality,zone, region) AS munizongion, announceType
FROM annoucements;
Very nice - it's been a while since I've done some DB-intensive work, and I find that I forget all about little functions such as COALESCE(). :o
Then i learned something new today 
Guess i need to spend more time with manual :rolleyes: