Results 1 to 14 of 14
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: convert int field to boolean

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    SELECT name,if(num_child > 0, "Yes","No") as has_child
    FROM tbClerks

    Why do you care if clerks have children?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  7. #7
    Join Date
    May 2004
    Posts
    144
    thanks guys, but what do you think about this, i just checking mathematical functions of MySQL & it comes to my mind:
    Code:
    SELECT name,CEIL(ABS(SIN(num_child))) AS has_child from tbClekrs
    it returns 1 for num_child>0 and 0 for num_child=0.
    funny idea?
    Last edited by bono56; 07-10-09 at 11:25.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by bono56
    thanks guys, but what do you about this, its just checking mathematical functions of MySQL & it comes to my mind:
    Code:
    SELECT name,CEIL(ABS(SIN(num_child))) AS has_child from tbClekrs
    it returns 1 for num_child>0 and 0 for num_child=0.
    funny idea?
    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.

  10. #10
    Join Date
    May 2004
    Posts
    144
    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.
    Last edited by bono56; 07-10-09 at 11:32.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by bono56
    i just wanted bool value. which one do you think is faster?
    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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56
    which one do you think is faster?
    there's only one way to find out...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    possibly more correct
    LOL!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2004
    Posts
    144
    thank u guys:>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •