| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8
Comments:
<0> you can do like this...
<0> select t1.field, t2.field from table1 t1, table2 t2 where.... '
<0> ;
<0> !m Madeye select
<1> Madeye: (SELECT Syntax) : http://dev.mysql.com/doc/mysql/en/SELECT.html
<2> hi, anybody awake
<3> hi! nobody!
<2> hi
<2> I'm having problems calculating the current age
<3> use your fingers :)
<3> !m mitu datediff
<2> that's a secure but a bit slow method
<1> mitu: (Date and Time Functions) : http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
<2> I have this query
<2> SELECT p***word,
<2> (YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5) < RIGHT(birthdate,5))
<2> AS edad_user, ***
<2> FROM users
<2> BUT the prblem is that bithdate could be null
<2> and then the query returns me 2006 as the age
<2> I've been trying with an IF clause
<2> but i can't make it work
<4> why not just use a coalesce ?
<2> ?
<2> what do you mean?
<5> is there something bigger than bigin ?
<5> *bigint
<3> Fleck: decimal! :)
<4> well usually if you want a value to be replaced with something else when it is null you use a coalesce
<4> Fleck: blob :P
<3> slayerbob: or ifnull()
<2> [domas] maybe exists ifnotnull() ?
<4> indeed
<4> why would there be an ifnotnull ?
<5> blob suports SUM() ?
<4> shouldn't think so Fleck :P
<4> but you did not specify that :P
<2> [slayerbob] I need to get the current age as I've pasted but bithdate could be Null so then my calculation of the current age becomes 2006 years old
<5> well i need numeric type
<4> well what do you want the age to become if the birthdate is null ?
<2> so I need that if bithdate is null then don't calculate the age or something like that
<4> you mean if it is null return a null ?
<2> maybe null slayerbob
<2> yes
<2> thats it
<4> well null used in a summation should return null
<4> so for example, null + 1 = null
<2> [slayerbob]
<2> SELECT p***word,
<2> (YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5) < RIGHT(birthdate,5))
<2> AS edad_user, ***
<2> FROM users
<2> returns 2006 as edad_user (user_age) if bithdate =""
<2> that's my problem I can't undestand how 2006-Null can return 2006
<4> it should return null
<4> unless i am confused somewhere
<2> I know slayerbob
<2> but it doesn't
<6> given this query: SELECT events.id FROM events RIGHT JOIN pictures ON events.id=pictures.event GROUP BY events.id, how would you get the listing of all events.id which are NOT in this list? :-/
<2> SELECT events.id FROM events WHERE id NOT IN (your query) oracle1
<6> oh such concatenated queries syntax..that was what i was looking for!
<6> lets see
<4> Mitu: are you sure that birthdate is actually null ?
<4> like is the column nullable ?
<6> mitu: thx
<4> because when i evaluate that expression in mysql here i get null
<4> well... replacing birthdate with null
<2> well ems mysql manages says it's a null
<2> unless they change the null value by a ""
<2> you know? it's null but not the null given by mysql
<2> maybe this can confuse mysql
<4> could be
<2> please try it slayerbob
<6> mitu: is that mysql 5 syntax?
<4> i was wondering about the "AS" bit myself
<2> [oracle1] I think so why=
<2> ?
<4> usually you have the as after each column
<2> [slayerbob] ?
<6> because it does not work ;) ..let me try harder.
<4> so it would be
<4> select p***word as edad_user, (long expression) as *** from users
<4> although i could be wrong :P
<2> nono slayerbob
<2> SELECT p***word,
<2> (YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5) < RIGHT(birthdate,5))
<2> AS edad_user, ***
<2> FROM users
<2> SELECT p***word,
<2> (YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5) < RIGHT(birthdate,5))
<2> AS edad_user
<2> FROM users
<2> that's all
<4> ?
<4> you've lost me now
<2> even you can take out p***word
<4> oh right
<4> yup now i see
<4> sorry i was getting confused :P
<4> lateness of the night and all :P
<4> thought it looked odd :P
<2> I mean the only I need is the edad_user
<4> yuppers i see that now :P
<6> SELECT events.id FROM events WHERE id NOT IN (1,2,3) works. but a SELECT in ( .. ) does not work
<2> [oracle1]
<4> what do you get if you replace that expression with coalesce(birthdate, "no birthdate specified") ?
<2> SELECT events.id
<2> FROM events
<2> WHERE id NOT IN (SELECT events.id
<2> FROM events RIGHT JOIN pictures
<2> ON events.id=pictures.event)
<4> does it return "no birthdate specified" or null for the null birthdates ?
<2> [slayerbob]
<2> how I do this
<4> if it returns NULL then there is a problem with the data in your database
<4> just type it in to mysql
<2> coalesce(birthdate, "no birthdate specified") <- THIS ?
<4> well no
<2> where ?
<2> into a query?
<4> you would want to put it in a select
<2> ok I'll try
<4> select coalesce(birthdate, "no birthdate specified") from users;
<4> if you see nulls then something is wrong
<7> can you tell me the max chars that can be held in a field? is it 255? or can there be more?
<4> since the coalesce should convert them all into the text string
<4> illegalc0de: much more
<7> i know "varchar" can only be 255
<4> illegalc0de: but it depends on the column
<4> i am told that varchars can be bigger than 255 now
<4> although i have not tested this myself as yet :P
<7> what would i need to change the field to? memo?
<2> [slayerbob]
<2> SELECT coalesce(birthdate, "no birthdate specified") AS edad
<2> FROM users
<7> slayerbob: the server is running an old virsion of mysql
<4> text has always been able to take 65535 characters
<4> or was it 65536 ?
<4> cannot quite recall :P
<2> this returns me the birthdate for each user
<7> ok thanks
<2> but the null ones are change for a "0000-00-00"
<4> ah
<4> so your birthdate column is not nullable
<4> and has a default of 0000-00-00
<2> wait a sec
<8> hey, what was the command to remove all data from a table again?
<4> truncate table nameoftablehere;
<4> perhaps ?
<2> [slayerbob] you're right
Return to
#mysql
or
Go to some related
logs:
smeserver7 vs
#linuxhelp
sodo aptitude install g++
#math
Kinnopix
#linuxhelp
#lisp
foxhacker
libdvdcs of ubuntu
Examples using LWP
|
|