#mysql
07 November 2007
Total 24 pages. You are browsing page 2/24.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
01:04 <****> MrKeuner: I provide hints, not your whole homework ;-)
01:05 <****> !tell us about tias
01:05 <****> arjenAU: :) OK thank you
01:05 <****> the above actually works for zero and nonzero. in the end any if needs to evaluate to a true or a false, whatever the expression.
01:05 <****> chadmaynard asked me to tell you this: TIAS: Try It And See. You look silly asking if something will work when you can just try it and see. This channel is not a MySQL server ;-)
01:05 <****> chadmaynard: and neither am i ;_0
01:06 <****> WaAcKoO: latin1 is fine
01:06 <****> thanks
01:06 <****> arjenAU: that's a convoluted smiley. I am unsure what emotion it conveys.
01:07 <****> chadmaynard: it conveys a typo
01:07 <****> arjenAU: i see
01:07 <****> hey kimseong
01:08 * ki77a77 thinks it looks like an F Sharp
01:08 <****> slb_: hows your problem?
01:08 <****> sitll a problem :)
01:08 <****> let me give it a thought later
01:08 <****> ok sounds good :)
01:09 <****> slb_: can you pastebin the tables structure again
01:09 <****> sure
01:11 <****> if i have "select * from foo where (a - b) < 10;" .. can i get mysql to output the value of (a - b) when it's less than 10?
01:12 <****> gose: select *, a-b from ....
01:12 <****> SELECT (a - b) as aminusb,...
01:13 <****> kimseong: sweet! thx!
01:13 <****> arjenAU: do I have to set the collation to something sepecific for english it says latin1_sweedish_ci right now.
01:13 <****> thanks arjenAU!
01:13 <****> WaAcKoO: that's fine
01:14 <****> basically it is just how it commuicates right? it shouldn't really affect the information itself
01:15 <****> arjenAU: I think I have to reask my question. I have a table with fields a,b,c and value. a can be true/false and b has values from set where cardinality of this set is 100. lastly c holds year {2000,2001,2002} table has values for each a for each b and for each c. I would like to list those values as two columns where column 1 is 2000 and column 2 is 2001
01:15 <****> arjenAU: your advice is giving me something like that but one value for each row, I need all values for each row
01:16 <****> MrKeuner: my advice was a hint, not the whole thing whatever it is.
01:16 <****> MrKeuner, run one query for 2000 and one query for 2001, then combine the columns in your application.
01:16 <****> you can do it in a single query
01:16 <****> just more of the same foo
01:17 <****> arjenAU: No it is OK if I can do it with if statements.
01:17 <****> arjenAU, I don't think so, not reasonably, for what he's asking.
01:17 <****> you can assemble horizontal cols not a prob
01:18 <****> using IFs, CASE, and so on. you can even base grouping on them.
01:18 <****> hey kim: http://pastebin.ca/764186
01:18 <****> ok
01:18 <****> MrKeuner, so basically you want to list all the rows with year=2000; and list all the rows with year=2001, right?
01:18 <****> Simetrical: yes
01:18 <****> better to group that actually, GROPU BY yearcol
01:19 <****> arjenAU, then you get one row for each year, I do believe.
01:19 <****> Simetrical: ahyes. true. anwyay, you can still do it without grouping, just with ifs in each col
01:19 <****> MrKeuner, so for the rows with year=2000, run: SELECT a, b FROM table WHERE c=2000;. For the rows with year=2001, do the same.
01:20 <****> http://www.ddj.com/database/202802994
01:20 <****> arjenAU, so that a typical row would be like "true 56 false 21", with the first two being for 2000 and the second two being for 2001? That's what he wants, I do believe.
01:20 <****> Simetrical: that's what i was doing and joining them in the spreadsheet but it is taking time since actually there are 30 years
01:20 <****> SELECT IF(yr=2000,CONCAT('a=',a,' b=',b)) AS yr2000values, ...
01:20 <****> MrKeuner, ah.
01:20 <****> Simetrical: I would like to get b as row name and year 2000 as col name 1 and 2001 as col name 2
01:21 <****> you can do what you like inside an IF()
01:21 <****> it's just an expression.
01:21 <****> and for a false you print ''
01:21 <****> so if it's not 2000, it'll leave an empty col in the output
01:21 <****> MrKeuner: rows have no name
01:21 <****> arjenAU, but it's an expression for the given row. You can't fill in data from two table rows in one result row, generally. Without a self-join, anyway, I guess.
01:22 <****> Simetrical: not an issue here, is it?
01:22 <****> arjenAU, it is if you want data from each year in the same row.
01:22 <****> oh he wants the year rows into cols. well, a self-join will do that nicely. indeed
01:22 <****> What join condition?
01:22 <****> depends on the rest of the row ;-)
01:23 <****> MrKeuner, basically, the format I think you want is not the format that relational databases use. Or anything closely resembling it.
01:23 <****> MrKeuner is not provided us will all relevant details.
01:23 <****> it does sound like a spreadsheet
01:23 <****> MrKeuner, a row in a result set corresponds to one record, or possibly multiple records that are somehow logically associated with one another.
01:24 <****> MrKeuner, the "proper" way to do this is basically to select a,b,c FROM table WHERE c IN (desired years); and then go through the result set in a loop in your programming language of choice.
01:24 <****> Simetrical: I see
01:25 <****> Simetrical: why? he can have the SQL format the output he wants
01:25 <****> MrKeuner, MySQL is a database query language, not a result-formatting language. It's expected that you use it in association with a proper programming language to format and display the results.
01:25 <****> arjenAU, not so easy in this case, from what I understand.
01:25 <****> true, but it's a choice still. it can be done
01:25 <****> arjenAU, you have to seriously contort things to get a given record to contain columns with no logical association.
01:25 <****> depends on whether they have in the end
01:25 <****> arjenAU, it can be done, yes, but it's going to be a lot messier than whipping out some Python or whatever.
01:25 <****> I presume theres another ID in the row
01:26 <****> otherwise there'd only be one row for each year anyway
01:26 <****> No, he said there were three columns.
01:26 <****> A year, a boolean, and a column with cardinality 100.
01:26 <****> He didn't say there was any unique index, for instance.
01:27 <****> there is no primary key
01:27 <****> It sounds like maybe a log-type format, not one necessarily amenable to having a unique index (given that there's no autoincrement).
01:29 <****> it is actualy eurostat data http://epp.eurostat.ec.europa.eu/portal/page?_pageid=1073,46870091&_dad=portal&_schema=PORTAL&p_product_code=MIGR_LB_WPCTZNAR
01:32 <****> can I use mysqldump to dump a query?
01:33 <****> MrKeuner: not sure mysqldump is the best tool for that job... you CAN specify a WHERE clause to mysqldump but you might want to look at "SELECT INTO OUTFILE"
01:34 <****> MrKeuner: Yes, sort of. But if you just want to capture SELECT output, use mysql --xml or the like.
01:34 <****> you can dump the output of a query, either with mysqldump or mysql cmdline tool
01:34 <****> MrKeuner: you should create a schema that's appropriate for your selects. you don't just insert the external data you get.
01:37 * arjenAU has a new apple, arjen has a new apple
02:05 <****> hi, I was wondering if someone could help me out with a few questions I have with using the command line with MySql
02:05 <****> I'm a complete newb, so you'll have to bare with me =]
02:06 <****> do we have to?
02:06 <****> Xye: you're not suggestioning we all get naked?
02:06 <****> yeah
02:06 <****> its coldish
02:06 <****> shrinkage
02:07 <****> nope, no nakedness
02:07 <****> and you don't have to, but I was told this would be the place to find some help =]
02:07 <****> liars
02:07 <****> cheats
Total 24 pages. You are browsing page 2/24.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last