Re: columns of one table are rows of another table
JC [EMAIL PROTECTED] wrote on 02/01/2006 01:15:00 PM: Hi all, I really need your help. I have two tables: table1: id1|1|2|3|4| 000+a+b+c+d 001+e+f+g+h . . . and table2: id2|col1|col2|col3 1+val1+val2+val3 2+val4+val5+val6 3 4 . . . columns (1,2,3,4,...) in table1 are rows (id2) in table2. I want to query rows in table2 such that id2 IN (all columns in table1 except first columns). Is this possible to do in one statement? I know how to do this in multiple queries, just wonder anyone knows how to optimize this. Thanks, JC -- I am afraid you can't write that kind of query in SQL. The syntax of the language just doesn't allow one value to be compared across two or more columns without some kind of major hack or a bunch of typing (something like val=col1 and val=col2 and ... and val=colN). May I suggest that you redesign table1 so that it looks like this: id1|t2_id|value Sure you end up with more rows but what you gain in flexibility should more than make up for the pittance of space you will need to store a bunch of extra row pointers. There is a relatively simple query pattern you can use to convert this new vertical design back to your original (pivoted) design SELECT id1, SUM(if(td2_id) = 1, value,0) as 1, SUM(if(td2_id) = 2, value,0) as 2, SUM(if(td2_id) = 3, value,0) as 3, ... SUM(if(td2_id) = N, value,0) as N FROM table1 GROUP BY id1; Perhaps if you described your situation more accurately, you could get a better response. We on the list are used to dealing with some rather complex designs and issues so don't feel at all like you need to dumb it down for us. The very fact that you tried to simplify your design actually made it harder to give you a decent answer. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: columns to rows
IF the dates are limited and can be agreed upon before running the kwiri, you can use: SELECT no, IF (date=d1, data, NULL) d1, IF (date=d2, data, NULL) d2, IF (date=d3, data, NULL) d3 FROM table GROUP BY no; It will also NOT work if one date can contain multiple data, e.g. No data date 1uyt d1 1abc d1 cheers /rudy -Original Message- From: Phil Evans [mailto:[EMAIL PROTECTED] Sent: vrijdag 11 juli 2003 17:12 To: [EMAIL PROTECTED] Subject: columns to rows Hi there. I am a rank amateur at this trying to make sense out of a heap (and growing) of data. I have a resultset with this structure: nodatadate 1uytd1 1klhd2 1oiud3 2kjhd1 2kljhd2 2asdd3 that I wish to convert to this structure. no d1d2d3 1 uytklhoiu 2 kjhkljh asd Given that the original has over 100,000 records, I was hoping to find some reasonable way of doing it. Thanking you, PhilE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: columns to rows
On Sat, Jul 12, 2003 at 01:11:41AM +1000, Phil Evans wrote: Hi there. I am a rank amateur at this trying to make sense out of a heap (and growing) of data. I have a resultset with this structure: nodatadate 1uytd1 1klhd2 1oiud3 2kjhd1 2kljhd2 2asdd3 that I wish to convert to this structure. no d1d2d3 1 uytklhoiu 2 kjhkljh asd something like: select no.no, d1.data, d2.data, d3.data from no, data as d1, data as d2, data as d3 where no.no = d1.no and no.no = d2.no and no.no = d3.no order by no.no; Good luck, Harmen Given that the original has over 100,000 records, I was hoping to find some reasonable way of doing it. Thanking you, PhilE -- The Moon is Waxing Gibbous (93% of Full) tty.nl - 2dehands.nl: 83414 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: columns to rows
Phil Evans [EMAIL PROTECTED] wrote: Hi there. I am a rank amateur at this trying to make sense out of a heap (and growing) of data. I have a resultset with this structure: nodatadate 1uytd1 1klhd2 1oiud3 2kjhd1 2kljhd2 2asdd3 that I wish to convert to this structure. no d1d2d3 1 uytklhoiu 2 kjhkljh asd Given that the original has over 100,000 records, I was hoping to find some reasonable way of doing it. You can't do it with MySQL only. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: columns to rows
I disagree, even though I had my own share of problems in compiling 4.0.13. The clue is in the error message configure:error: no acceptable C compiler found in $PATH It is very likely that if he typed which cc or which gcc the reply would come back No cc (gcc) in ... (a list of directories). Either gcc is not installed or the PATH variable is set incorrectly. To check how the path variable is set, type echo $PATH Take care of that problem and try the install again. Emile State on 7/11/03 10:24 AM, harm at [EMAIL PROTECTED] wrote: On Sat, Jul 12, 2003 at 01:11:41AM +1000, Phil Evans wrote: Hi there. I am a rank amateur at this trying to make sense out of a heap (and growing) of data. I have a resultset with this structure: nodatadate 1uytd1 1klhd2 1oiud3 2kjhd1 2kljhd2 2asdd3 that I wish to convert to this structure. no d1d2d3 1 uytklhoiu 2 kjhkljh asd something like: select no.no, d1.data, d2.data, d3.data from no, data as d1, data as d2, data as d3 where no.no = d1.no and no.no = d2.no and no.no = d3.no order by no.no; Good luck, Harmen Given that the original has over 100,000 records, I was hoping to find some reasonable way of doing it. Thanking you, PhilE -- Emile Marion State 132 Thornway Ave Thornhill, ON L4J 7Z3 (905) 669-5652 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: columns no more
John, Saturday, May 11, 2002, 3:13:33 AM, you wrote: JD is there a way to delete a column from a table, without droping the whole JD table? Sure, you can use ALTER TABLE: http://www.mysql.com/doc/A/L/ALTER_TABLE.html JD If not is can I get sql to replicate the command to re create the table? JD and if I can't do that how can I transfer information to a dummy table while JD I create the table I want? CREATE .. SELECT? Look at: http://www.mysql.com/doc/C/R/CREATE_TABLE.html JD PS, I only have command line access JD J -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: columns no more
is there a way to delete a column from a table, without droping the whole table? Yep! ALTER TABLE tablename DROP [COLUMN] col_name http://www.mysql.com/doc/A/L/ALTER_TABLE.html If not is can I get sql to replicate the command to re create the table? Yep! mysqldump -qd -u user database tablename http://www.mysql.com/doc/m/y/mysqldump.html can I transfer information to a dummy table while I create the table I want? Yep! Just do a select into outfile http://www.mysql.com/doc/S/E/SELECT.html then a load data infile http://www.mysql.com/doc/L/O/LOAD_DATA.html Take care, seth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Columns
Try 1) create table c as select a,b as b1,b as b2 ... from a 2) drop a; 3) alter table c rename to a; Instead of 2) you can rename the table and drop later, or tar-up the .MYI,.MYD and .frm files for the table before doing the above. Regards, Dan -Original Message- From: Keith A. Calaman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 13 February 2002 5:08 a.m. To: Max Mouse; [EMAIL PROTECTED] Subject: RE: Columns Sounds like an UPDATE: http://www.mysql.com/doc/U/P/UPDATE.html UPDATE TABLE SET columnname1 = columnname2 where KEY = KEY Something like that probably. If it was me I would copy the whole table so I had a backup...UPDATES and DELETES can be destructive if writting improperly (*_*) -Original Message- From: Max Mouse [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 4:11 PM To: [EMAIL PROTECTED] Subject: Columns Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Columns
Sounds like an UPDATE: http://www.mysql.com/doc/U/P/UPDATE.html UPDATE TABLE SET columnname1 = columnname2 where KEY = KEY Something like that probably. If it was me I would copy the whole table so I had a backup...UPDATES and DELETES can be destructive if writting improperly (*_*) -Original Message- From: Max Mouse [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 4:11 PM To: [EMAIL PROTECTED] Subject: Columns Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns
On Wed, Feb 06, 2002 at 04:10:33PM -0500, Max Mouse wrote: Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Something like: UPDATE mytable SET col2 = col1, col3 = col1, col4 = col1, col5 = col1; perhaps? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 2 days, processed 105,996,493 queries (469/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
Erling and MySQL guys, On Fri, Mar 16, 2001 at 02:18:55AM +0100, Erling Paulsen wrote: I just exported (via myodbc) an old access database for my sportsclub to mysql (the tables). The ms-access database application still works nicely on the new linked tables in mysql. However, I'm now also writing a php based client to use the database over the web and the problem is as follows: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! The mysql client doesn't know about backticks and therefore handles the # (and '-- ') as the beginning of a comment. It wouldn't do that for quoted and double-quoted strings. I see no workaround other than to change the client/mysql.cc source and recompile. Line 983 of client/mysql.cc (in the add_line function) should be changed from: else if (!*in_string (inchar == '\'' || inchar == '"')) to else if (!*in_string (inchar == '\'' || inchar == '"' || inchar == '`')) This is for version 3.23.33. The line number may be different for other versions. If you want to see the correct continuation character for multi-line backticks, you would change a few occurrances in read_lines of: tee_fputs(glob_buffer.is_empty() ? "mysql " : !in_string ? "- " : in_string == '\'' ? "' " : "\" ",stdout); to tee_fputs(glob_buffer.is_empty() ? "mysql " : !in_string ? "- " : in_string == '\`' ? "` " : in_string == '\'' ? "' " : "\" ",stdout); I cc'ed this to the bugs mailing list, so it may be fixed in a next release. Looking at the sources, the MySQL server will handle this just fine, so your PHP scripts should run unless PHP does its own parsing of SQL. (I never use PHP, but I guess it does no such thing). Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
On Fri, Mar 16, 2001 at 10:33:05AM +0100, Fred van Engen wrote: The mysql client doesn't know about backticks and therefore handles the # (and '-- ') as the beginning of a comment. It wouldn't do that for quoted and double-quoted strings. I see no workaround other than to change the client/mysql.cc source and recompile. Oh yes, note that I didn't test these source changes, but they seem simple enough. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
At 2:18 AM +0100 3/16/01, Erling Paulsen wrote: I just exported (via myodbc) an old access database for my sportsclub to mysql (the tables). The ms-access database application still works nicely on the new linked tables in mysql. However, I'm now also writing a php based client to use the database over the web and the problem is as follows: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! Use backticks around the column name when you refer to it: `Person#` -- Erling Paulsen Norway -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Columns named with the # character
I've tried to escape it and also to `bakctick` it - neither seems to work. ex1- backticks: mysql select * from temp01 where `Post#` = 9019; - - It does not compute as finnished and prompts for more, when I finish manually it says: - ; ERROR 1054: Unknown column 'Post' in 'where clause' - ex2 - escaped: mysql select * from temp01 where Post\# = 9019; ERROR 1054: Unknown column 'Post' in 'where clause' - I don't know what to do! I'm wondering if it might have something to do with the characterset? I'm using latin1. The last thing I wanna do is rename all the columns. MYSQL Server Info: mysql status -- mysql Ver 11.12 Distrib 3.23.33, for pc-linux-gnu (i686) Connection id: 131 Current database: tkk Current user: root@localhost Current pager: = 9019; Using outfile: '' Server version: 3.23.33 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 1 day 17 hours 47 sec Threads: 19 Questions: 2457 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 19 Queries per second avg: 0.017 -- - Original Message - From: "Sam Smith" [EMAIL PROTECTED] To: "Erling Paulsen" [EMAIL PROTECTED] Sent: Friday, March 16, 2001 3:21 AM Subject: Re: Columns named with the # character On Fri, 16 Mar 2001, Erling Paulsen wrote: They guy that developed the access database used `#' (comment character) characters to name certain columns. i.e. "Person#". How can manually select such a column in a mysql query (ex. via the mysql client)? - since it seems whatever i enclose the column name in, the rest of the query is interpreted as a comment! try \# -- the \ should remove the specialness of the # Although the number of \ may have to be increased, depending on what it is you are actually doing, and how many times your \ and # are going to get interpretted. Regards Sam -- Disservice: It Takes Months to Find a Customer, But Only Seconds to Lose One. The Good News is We Should Run Out of Them In No Time. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
