i have tbClerks like this:

name num_child
mark 3
eli 1
colins 2
mary 0

now i want a view like this:
name has_child
mark yes
eli yes
colins yes
mary no

i know i can add 'has_child' field, fill it with an update statement, & then use it, but i wonder is it possible to make it happen without adding field.
i try this one but it didnt work:

SELECT name,CAST(num_child AS boolean)
FROM tbClerks

SELECT name,if(num_child > 0, "Yes","No") as has_child
FROM tbClerks

Why do you care if clerks have children?

bastard syntax supported only by mysql -- if(num_child > 0, "Yes","No")

standard sql, also supported by mysql -- CASE WHEN num_child > 0 THEN 'Yes' ELSE 'No' END

come on, mike, learn standard sql, that way you will have so much less to unlearn if you ever work with any other database system

We're in a MySQL forum with a question on MySQL - I figured I was pretty safe going for a MySQL response. I'm guessing that most smaller projects once started in MySQL will never leave MySQL (why should they?). So why ignore half the syntax? Even in large companies when they decide to change their main RDBMS supplier it really just means that new projects will use the new RDBMS. Few people are insane enough to go to the expense of rewriting an existing system to use a new RDBMS for no real gain.

I personally work with 3 different RDBMs (Sybase, MS SQL and Oracle) - the MySQL bit I only do as a hobby as there isn't much money in it. 18 minutes before you responded to one of my posts with a near identical bit of SQL - are you getting a little slow Rudy?

when there are two ways to write something in mysql, choosing to write it in standard sql is a no-brainer, and i'm surprised to see you arguing for the opposite

yes, i occasionally use proprietary mysql syntax myself (e.g. i absolutely ~love~ GROUP_CONCAT), but ~not~ if there is a standard sql equivalent

i'm just encouraging you to adopt best practice, mike, but hey, everyone gots to suit themselfs and maybe keeping best practice in mind is too taxing for some people...

I guess it's best to simply allow people to help others in what ever way they can. If posters do make a mistake then sure, correct them. However if it's just a difference in style then is it really worth arguing over? However if there is a serious error in using some MySQL commands then it might be best to argue your case with the MySQL developers, rather than those that use (and quite like) the language.

thanks guys, but what do you think about this, i just checking mathematical functions of MySQL & it comes to my mind:
it returns 1 for num_child>0 and 0 for num_child=0.
funny idea?

it certainly fails to meet the original requirement, which was to return Yes/No, not 1/0

it's also significantly obscure

it may work, but it's a hack, and not a very good one

I'm sure Rudy will be much happier with this

Though traditionally we rarely calculate the sine (cosine or tangent) of our children. When they get a little annoying I do occasionally get the urge to just floor them - I find this restores peace and order around the house almost immediately.

True/False, Yes/No or 0/1 is not issue, i just wanted bool value.
which one do you think is faster? i have about 400,000 record.

Your method will be slowest, mine and Rudy's solution will be about the same and this will be marginally quicker and possibly more correct given the new info:

Code:

SELECT name,
       num_child > 0 as has_child
FROM   tbClerks

there's only one way to find out...

LOL!!!

thank u guys:>