#mysql
17 October 2007
Total 30 pages. You are browsing page 2/30.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
01:10 <****> pulse00, what do you mean by "make of each entry in SHORT_DESCR a Column" ?
01:10 <****> fg3, only here
01:11 <****> gnari, fixed typo http://dpaste.com/22679/
01:12 <****> gnari, i'm having a table with meta_data about values in the main table. for every entry in the main table, i have enter the id of the corresponding type in the meta-table. my problem is now i would need to put all the meta_data types in one column...
01:12 <****> pulse00, so, um, you basically want each row to return as a column?
01:13 <****> sorry, not "all the meta_data types in one column", but a colum for each entry in the table
01:13 <****> why would you want to do that?
01:14 <****> to be able to put it in an html table
01:14 <****> sorry, having slight difficulties here explaining this problem ;)
01:14 <****> should an INSERT INTO/SELECT FROM w/ an ON DUPLICATE KEY UPDATE after a left join be dropping records from the from table in the resulting table?
01:14 <****> pulse00, well, um, how about getting it as rows, and formating it into html however you want to?
01:15 <****> fg3, so in your queries, the elements you want to OR or AND together are the bit after the last AND ?
01:15 <****> yes
01:15 <****> pulse00: If you have a fixed list of types (DESCR(n) values) you wish to obtain, you can perform an N-way join, one for each different DESCR youplan to obtain.
01:15 <****> gnari, I placed them there to try to make it easier
01:16 <****> fg3, the first difficulty is that different fields require different tables to be joined in
01:16 <****> Xgc, i think that's what i'm trying to do...
01:16 <****> sorry, really a newbie here with databases
01:17 <****> gnari, I hear you
01:17 <****> pulse00: The joins I refer to are N joins against entry_type for each type. You only joined with entry_type once.
01:18 <****> fg3, that can be solved by changing those joins into left joins, and adding some logic to handle the extra NULLs
01:18 <****> Xgc, thanks for the hint, i'll try that
01:18 <****> gnari, sounds like a hairy problem when trying to build the SQL
01:19 <****> fg3 but it's probably a LOT easier to just split out the codes in the application. in perl, you'd use the split() function.
01:19 <****> probably faster to do it in the app too.
01:19 <****> cas__, not following ( i know perl)
01:19 <****> pulse00: The above suggestion (to construct the final tabular output programmatically in your app) is the normal way to handle it. The join approach doesn't scale well.
01:19 <****> Xgc: Thanks for the tips. I see now that the backup might not have been restored correctly, but I appreciate your time :)
01:19 <****> fg3, the first thing you must do is to change all the joins into explicit ones. mixing explicit and implicit joins is bad
01:20 <****> weedar: No problem.
01:20 <****> gnari, if I could see an example I would be most grateful
01:21 <****> fg3, i assume that instead of having one boolean field for each short desc, you have just one field with a list of codes that describe the record?
01:21 <****> fg3, ok. can i assume the parts of the WHERE upto the last AND will always be the same for our purpose?
01:21 <****> Xgc, yeah, i thought that might be less trouble, didn't know though if this could be handled without any big hassle with a query
01:21 <****> gnari, I think so
01:21 <****> if so, then it's easier to fetch that field, split it, and use it in the app than to try to make mysql split it up for you.
01:21 <****> fg3, ok. wait a minute
01:21 <****> gah ok this mysql stuff is harder then it looks
01:21 <****> gnari, ok
01:22 <****> re
01:22 <****> how do i search for a record in the file_name column that starts with swordfish or Swordfish or SWORDFISH
01:22 <****> cas__, I'm sorry - not following
01:22 <****> WHERE `field` LIKE 'swordfish%'
01:23 <****> bbelt16ag: ^^
01:24 <****>
01:24 <****> ok
01:24 <****> pulse00: You can also use GROUP_CONCAT() to construct a single field in the result for each type found. I know you said that's not what you wanted, but it one option some people find useful.
01:25 <****> anyone know why when i use ndb_restore to restore a backup, it claims to have successfully restored all the tuples, but when i do a "show databases;" in mysql, it lists ONLY two databases: 'mysql' and 'ndb_2_fs'?
01:25 <****> keeps saying empty set
01:25 <****> bbelt16ag: pastebin your whole query
01:26 <****> bbelt16ag: and no, that doesn't mean paste it in this channel.
01:26 <****> bbelt16ag: try ILIKE rather than LIKE
01:26 <****> ILIKE is case-insensitive LIKE
01:27 <****> cas__: surely you're not referring to mysql?
01:28 <****> litheum: not sure, i know postgresql's dialect of SQL a lot better than i know mysql's.
01:28 <****> bbelt16ag: You can always force the collation in the query.
01:29 <****> is there a 'recommended' charset to use, for 'global' languages usage/support ?
01:29 <****> cas__: in mysql, you would tell it to use a different collation. there's no ILIKE operator
01:29 <****> is there anyway i can replicate a line from one table to the other but one column in an easy way or will i have to do a SELECT and then an INSERT?
01:29 <****> duke-_: utf8, of course, is recommended for that situation
01:29 <****> duke-_: utf-8
01:29 <****> yea, but what utf8 ? _bin ? unicode_ci ?
01:29 <****> bbelt16ag: http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
01:29 <****> duke-_: those are collations. it's all pretty well explained in the manual... it depends *entirely* on what you're doing
01:30 <****> litheum well, as far as i managed to read.... _bin supports everything. unicode_ci have trouble with few languages and its case_insensetive ?
01:32 <****> can i make a trigger execute for a table when a row is deleted because it's foreign key was deleted?
01:34 <****> fg3: just select SHORT_DESC and split it into SHORT_DESC_A, SHORT_DESC_B, SHORT_DESC_C etc in the app.
01:34 <****> cas__, I think we are talking about diff problems
01:35 <****> solution to my earlier problem, gave up on group by and used join onto self. :) select distinct a.name from ads a,ads b where a.name=b.name and if(a.no,0,1)!=if(b.no,0,1);
01:36 <****> dkr: I used those sometimes.
01:36 <****> dkr: and it runs pretty fast too.
01:37 <****> fg3, you still there?
01:37 <****> gnari, yes
01:38 <****> fg3, then take a look at : http://dpaste.com/22683/
01:39 <****> gnari, wow! - thank you
01:40 <****> fg3: possibly. you probably need to explain what you want to do a bit better. or i need a working brain after wrestling with ndb_restore most of the night.
01:40 <****> cas__, thx for trying but I thing gnari has it nailed
01:41 <****> is there anywhere I can grab a clue as to how to design a schema for scheduling appointments ?
01:41 <****> gnari, thx again!!
01:41 <****> gnari, I will test tonight
01:41 <****> cas__, you realize that the SHORT_DESC_A, SHORT_DESC_B question came from pulse00 , not fg3 ?
01:41 <****> Note
01:41 <****> Currently, triggers are not activated by cascaded foreign key actions. This limitation will be lifted as soon as possible.
01:42 <****> will it ever be lifted?
01:42 <****> litheum: my brian hurts.
01:42 <****> s/brian/brain/
01:42 <****> docs have said so for years
01:42 <****> fg3, do you understand the logic ?
01:43 <****> ok why cant i use thwo where statmenents on one line..
01:44 <****> bbelt16ag, you can, as long as they belong to 2 different queries
01:44 <****> when designing a table and I have a unique field like "userid" which is a character field, is there any need to also include a non null integer field as a unique numeroc id? or is it just fine and just as fast to foreign key to this table using the unique character field?
01:45 <****> rgr, for this field to be a candidate, it must be NOT NULL in addition to UNIQUE
01:46 <****> rgr, there may be pros and cons for using this field as primary key
01:47 <****> gnari: it is both non null and unique. It will generally be simply a 3-5 character login ID.
01:47 <****> rgr, pro: if you want the database to enforce the uniqueness, you will need an index anyways, to the artificial key will only be extra overhead
01:48 <****> rgr, pro: using a natural key instead of an artificial key will sometimes allow you to do simpler queries (one less join)
01:48 <****> gnari, it looks clear - but I'll spend some time with it tonight
01:49 <****> rgr: con: if the varchar is sometimes very long, the foreign keys will take more space
01:50 <****> rgr, con: if you imagine you want to allow your users to change the userid, that will be a lot easier with the artificial key
01:50 <****> gnari: if its a primary key its already an index.
Total 30 pages. You are browsing page 2/30.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last