Re: comparing two databases
Is there a program out there that I can use to compare two databases? Just the structure, not the content. You might want to look into our development tool Database Workbench, it has a database compare tool as well: www.upscene.com Here's a screenshot/help: http://www.upscene.com/documentation/dbw/tools_schemacompare.htm Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regular Exp help
Dear All, I have a table with a varchar column that stores data in this pattern: numberone_white_spacestringone_white_spacehyphen_symbol The pattern may be repeated upto 5 times in same cell, but the string will be different in each case. Hence there will not be a value like this: 1 BW - 2.5 BW - 1 WT - 1 BW - 1 ME - 1 BW - 1 ME - 1.5 SY - 1 BW - 1 WT - 1 OT - 2.5 WE - 1 OT - 1 SY - 1 WT - 1 IT - 1 OT - I need a regular exp or any other query to fetch sum of numbers before a string. For example the result should be 2.5 if I am searching for SY and 3 if I am searching for WT. I tried a bit, but could not succeed. I am using 4.1.14-standard-log. The table is huge, hence the query should be streamlined enough. Please help. Thanks, Ravi.
Re: Regular Exp help
Ravi, Knight 4 to Pawn's 5! Sorry, being serious for a minute, you'd need more info to solve this problem. Your example implies that something without a number after it still counts for a value of 1, i.e. and 3 if I am searching for WT. Correct? What is the extent of the two letter combos? Are we talking just WT, BW, ME, SY, WE, OT and IT or are we talking about the whole space of AA to ZZ? You say the table is huge? 1 million records? 10 million? Just a rough ball park are these Var chars indexed? Just off the top of my head and without knowing what the actual context of the problem is I have to say it looks very difficult to do in pure SQL, not impossible just very difficult. Can you not use some glue code? Perl would be an ideal language, PHP would also be fine, any language would be better than SQL to be honest as it's just the wrong tool. I have a table with a varchar column that stores data in this pattern: numberone_white_spacestringone_white_spacehyphen_symbol The pattern may be repeated upto 5 times in same cell, but the string will be different in each case. Hence there will not be a value like this: 1 BW - 2.5 BW - 1 WT - 1 BW - 1 ME - 1 BW - 1 ME - 1.5 SY - 1 BW - 1 WT - 1 OT - 2.5 WE - 1 OT - 1 SY - 1 WT - 1 IT - 1 OT - I need a regular exp or any other query to fetch sum of numbers before a string. For example the result should be 2.5 if I am searching for SY and 3 if I am searching for WT. I tried a bit, but could not succeed. I am using 4.1.14-standard-log. The table is huge, hence the query should be streamlined enough. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing two databases
On Thu, 2006-09-28 at 15:06 -0500, Steve Buehler wrote: Is there a program out there that I can use to compare two databases? Just the structure, not the content. SqlFairy (http://sqlfairy.sourceforge.net/) has a sqlt-diff tool that will output the differences as a set of sql alter statements. Easiest way to install on nix box is cpan: $ sudo cpan SQL::Translator). mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with subselect and primary keys
Can someone tell me what's wrong with this test: create table t1 ( course_id int(10) signed not null, primary key (course_id) ); create table t2 ( course_id int(10) unsigned not null, primary key (course_id) ); insert into t1 values (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68); insert into t2 values (65),(66),(67); select distinct course_id from t1 where course_id not in (select course_id from t2); drop table t1; drop table t2; Running on 4.1.13 on SUSE Linux, this doesn't print anything. It should print those values in t1 but not t2. If I replace the subselect with the result of the subselect (65,66,67) then it works as expected. It also works if I remove the primary key from t2. If I just remove the primary key from t1 it prints a somewhat mysterious '1'. Can anyone explain what's going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Encryption
Is there a simple way to encrypted data as it's being stored in a table? And then easily decrypted when it's queried? Sample syntaxs if available - thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Encryption
Hello, Have you taken a look at: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html That might give you a good start. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Friday, September 29, 2006 8:14 AM To: mysql@lists.mysql.com Subject: Encryption Is there a simple way to encrypted data as it's being stored in a table? And then easily decrypted when it's queried? Sample syntaxs if available - thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updating table but afterwards there are duplicate entries that violate a key..
Hi all, I have a table like this TABLE -- tagid taggerid objectid There is a primary key on (tagid, taggerid, objectid). First I remove that key, then I am changing the object id (because I've changed my objects), but what happens now is that I suddenly have duplicate (tagid, taggerid, objectid) rows. Is there a way to remove duplicate rows like this? Thanks for any pointers!! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT wird unterschiedlich ausgeführt
Hallo zusammen! Ich habe die Frage vor ein paar Tagen bereits ins MySQL-Forum gestellt, aber leider hat da noch niemand geantwortet. Jetzt versuche ich es mal mit der Liste. Wäre super, wenn mit jemand von Euch helfen könnte, weil ich hier nach Wochen des Probierens nicht weiterkomme: Ich habe hier ein ganz komisches Verhalten einer Abfrage (Der Code ist unten). 1. Wenn ich die Abfrage direkt auf der DB ausführe, funktioniert sie perfekt 2. Wenn sie innerhalb des Programms mit einem CREATE TABLE oder INSERT INTO abläuft, liefert sie ein anderes Ergebnis: Ich habe zwei Querys. Die erste liefert mir einer temporary table, die in der zweiten dann per LEFT JOIN eingebunden wird. Die erste Query macht keine Probleme und liefert z.B. folgendes Ergbnis: 673|2006-11-20|29|NULL|12 Wenn die SELECT der zweiten Anweisung direkt z.B. über PHPMyAdmin abgesetzt wird, arbeitet sie richtig und liefert z.B.: 673|2006-11-20|12|1128|2006-11-12|0|6|6|6|1416|2006-11-24|4|0|2006-11-24|red Läuft sie dagegen mit einem CREATE TABLE oder INSERT INTO (auch aus PHPMyAdmin heraus) liefert sie: 673|2006-11-20|12|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|0|NULL|green Der Knackpunkt scheint mir die Variable @q3 zu sein, die nicht berechnet wird; nur warum nicht?!?!?!?!? Ich hoffe, mit kann jemand weiterhelfen! Kurz noch was zum Hintergrund: Die erste Abfrage liefert den aktuellen Zustand unserer Produkte sowie die Dauer der einzelnen noch nicht erledigten Tätigkeiten in Stunden. Die zweite Abfrage soll die Summe der ausstehenden Tätigkeiten errechnen, und daraus mit einem Faktor einen voraussichtliches Enddatum der Produktion errechnen. Es sollen Wochenenden berücksichtigt werden. Fällt das Enddatum auf ein Wochenende, so soll das Enddatum auf den nächsten Werktag verschoben werden. Wie gesagt, im Pronzip funktioniert das! MySQL-Version: 4.0.18-max-nt (geht leider nicht anders!) Ich habe die zweite Abfrage mal so umgebaut, dass keine Variablen mehr vorkommen und nur noch 3 Spalten erzeugt werden. Leider ändert dies aber nichts amVerhalten :-( Hier der Code: $query = 'CREATE TEMPORARY TABLE dauertemp1 (INDEX index1 (prozessid,dauer_neu)) SELECT DISTINCTROW a.id as prozessid, a.zieltermin, b.schrittnr_modul, b.schrittnr_basisschritt, c.dauer_neu FROM prozess a LEFT JOIN prozessprotokoll b ON (b.prozessid=a.id) LEFT JOIN basisschritt c ON (c.id=b.basisschrittid) WHERE (a.erledigt IS NULL OR (a.erledigt IS NOT NULL AND a.gesperrt IS NOT NULL)) AND b.erledigt IS NULL '.$where; $db-query($query,__LINE__,__FILE__); $query = 'CREATE TEMPORARY TABLE dauertemp2 (INDEX index1 (prozessid,endtermin)) SELECT prozessid, @z:=zieltermin as zieltermin, @q1:=SUM(dauer_neu) as SummeStunden, @q3:[EMAIL PROTECTED] as AnzahlStunden, @q2:=DATE_ADD(CURRENT_DATE(), INTERVAL ROUND(@q3/24) DAY) as ZielTermin1, @y1:=YEAR(@q2)-YEAR(CURRENT_DATE()) as JahresVergleich, @q4_1:=WEEK(@q2,1)-WEEK(CURRENT_DATE(),1) as AnzahlWEGleichesJahr, @q4_2:=52-WEEK(CURRENT_DATE(),1)+(@y1-1)*52+WEEK(@q2,1) as AnzahlWEDiffJahre, @q4:=IF(@y1=0,@q4_1,@q4_2) as AnzahlWochenenden, @q5:[EMAIL PROTECTED]@q4*48 as AnzahlStundenInclWochenenden, @q6:=DATE_ADD(CURRENT_DATE(), INTERVAL ROUND(@q5/24) DAY) as ZielTermin2, @q7:=WEEKDAY(@q6) as ZielWochentag, @q8:=IF(@q74,[EMAIL PROTECTED],0) as TageFuerVerschiebungWochenende, @e:=CAST(DATE_ADD(@q6, INTERVAL @q8 DAY) as DATE) as endtermin, IF(@z@e,red,green) as color FROM dauertemp1 GROUP BY prozessid'; $db-query($query,__LINE__,__FILE__); Ciao und danke! Peter ___ Viren-Scan für Ihren PC! Jetzt für jeden. Sofort, online und kostenlos. Gleich testen! http://www.pc-sicherheit.web.de/freescan/?mc=02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating table but afterwards there are duplicate entries that violate a key..
Peter Van Dijck wrote: Hi all, I have a table like this TABLE -- tagid taggerid objectid There is a primary key on (tagid, taggerid, objectid). First I remove that key, then I am changing the object id (because I've changed my objects), but what happens now is that I suddenly have duplicate (tagid, taggerid, objectid) rows. Is there a way to remove duplicate rows like this? Thanks for any pointers!! Peter Use ALTER TABLE IGNORE and put the key back. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
Hi All,
I have 2 separate mysql servers and need to import data from a table on
sever1 to
a table on server2. This would need to be done in Perl.
The script in question already has open handles to both servers, so I
know I can
select all the rows from server1 and process them one by one and insert
into the table on server2, but I was wondering if there was a more
simple way to do this which would allow me to transfer the data from one
database handle directly to another?
Looking on google, the closest example I can find is something like :
#!/usr/bin/perl
use DBI;
$dbh1=.;
$dbh2=.;
...
$statement = select a,b,c,d,... from table1 where condition='$value';
$sth=$dbh1-prepare($sql);
my @results;
while (@results = $sth-fetchrow_array) {
# build placeholders based on num of fields
my $placeholders;
$placeholders .= ($placeholders ? ,? : ?) for (@results);
my $sth2 = $dbh2-prepare(INSERT INTO table2 values
($placeholders););
$sth2-execute(@results);
$sth2-finish;
}
$sth1-finish;
$dbh1-disconnect();
$dbh2-disconnect();
George Law
[EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
Phone: 864-678-3161
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with subselect and primary keys
Derek, I was able to replicate all the behaviors you describe in 5.0.21. I noticed you have a signed INT in one table and an UNsigned INT in the other. I changed t1 to UNsigned and then the query returns the results you would expect: +---+ | course_id | +---+ |-2 | |-1 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | |68 | +---+ seems like the signed/unsigned data is not being converted before comparison, perhaps. If you can't change your column type in the table, perhaps you could use the CAST function in your queries? HTH, Dan On 9/29/06, Derek Fountain [EMAIL PROTECTED] wrote: Can someone tell me what's wrong with this test: create table t1 ( course_id int(10) signed not null, primary key (course_id) ); create table t2 ( course_id int(10) unsigned not null, primary key (course_id) ); insert into t1 values (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68); insert into t2 values (65),(66),(67); select distinct course_id from t1 where course_id not in (select course_id from t2); drop table t1; drop table t2; Running on 4.1.13 on SUSE Linux, this doesn't print anything. It should print those values in t1 but not t2. If I replace the subselect with the result of the subselect (65,66,67) then it works as expected. It also works if I remove the primary key from t2. If I just remove the primary key from t1 it prints a somewhat mysterious '1'. Can anyone explain what's going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
George, that's probably about the easiest way you could do it in perl.
If you want every column transferred you could do a SELECT * instead
of enumerating columns I think.
Dan
On 9/29/06, George Law [EMAIL PROTECTED] wrote:
Hi All,
I have 2 separate mysql servers and need to import data from a table on
sever1 to
a table on server2. This would need to be done in Perl.
The script in question already has open handles to both servers, so I
know I can
select all the rows from server1 and process them one by one and insert
into the table on server2, but I was wondering if there was a more
simple way to do this which would allow me to transfer the data from one
database handle directly to another?
Looking on google, the closest example I can find is something like :
#!/usr/bin/perl
use DBI;
$dbh1=.;
$dbh2=.;
...
$statement = select a,b,c,d,... from table1 where condition='$value';
$sth=$dbh1-prepare($sql);
my @results;
while (@results = $sth-fetchrow_array) {
# build placeholders based on num of fields
my $placeholders;
$placeholders .= ($placeholders ? ,? : ?) for (@results);
my $sth2 = $dbh2-prepare(INSERT INTO table2 values
($placeholders););
$sth2-execute(@results);
$sth2-finish;
}
$sth1-finish;
$dbh1-disconnect();
$dbh2-disconnect();
George Law
[EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
Phone: 864-678-3161
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1
| Hi All,
|
| I have 2 separate mysql servers and need to import data from a table on
| sever1 to
| a table on server2. This would need to be done in Perl.
|
| The script in question already has open handles to both servers, so I
| know I can
| select all the rows from server1 and process them one by one and insert
| into the table on server2, but I was wondering if there was a more
| simple way to do this which would allow me to transfer the data from one
| database handle directly to another?
|
| Looking on google, the closest example I can find is something like :
|
| #!/usr/bin/perl
| use DBI;
|
| $dbh1=.;
| $dbh2=.;
| ...
|
| $statement = select a,b,c,d,... from table1 where condition='$value';
| $sth=$dbh1-prepare($sql);
| my @results;
| while (@results = $sth-fetchrow_array) {
|
| # build placeholders based on num of fields
| my $placeholders;
| $placeholders .= ($placeholders ? ,? : ?) for (@results);
|
| my $sth2 = $dbh2-prepare(INSERT INTO table2 values
| ($placeholders););
| $sth2-execute(@results);
| $sth2-finish;
| }
|
| $sth1-finish;
| $dbh1-disconnect();
| $dbh2-disconnect();
|
| George Law
| [EMAIL PROTECTED]
| MSN: [EMAIL PROTECTED]
| Phone: 864-678-3161
George,
Did you try to INSERT INTO db2.tablename SELECT fields from db1.tablename
where field='Somevalue' across different servers? Don't know if it's
possible but it would certainly be more efficient.
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: comparing two databases
Some freebies: PHP: http://sourceforge.net/projects/phpmycomparer Perl: http://freshmeat.net/projects/mysqldiff/ Cheers, Andrew -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: Thu, 28 Sep 2006 21:06 To: mysql Subject: comparing two databases Is there a program out there that I can use to compare two databases? Just the structure, not the content. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed
Hi I'm having a problem compiling store procedure getting errors that don't make much sense. The problem is that I need to filter any value that is either NULL or an empty string an set to '0' character. SP is below CREATE PROCEDURE test () BEGIN DECLARE code VARCHAR(10) DEFAULT '0'; update table_name set filed_name = CASE when ISNULL(filed_name) = 1 OR CHAR_LENGTH(filed_name) = 0 then code END CASE; END; Thanks for any help.
Quick way to determine existence of an index?
Dear MySQL-ers, Using MySQL 5.0.24a, is there a quick way to determine which columns on a table don't have an index? I want to do sort of s recursive loop, and add indices for all columns which don't have an index yet (and that over many databases). Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running a Staging and Development DB on the same server?
I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick way to determine existence of an index?
Mark wrote: Dear MySQL-ers, Using MySQL 5.0.24a, is there a quick way to determine which columns on a table don't have an index? I want to do sort of s recursive loop, and add indices for all columns which don't have an index yet (and that over many databases). Thanks, - Mark Sounds like an extremely bad idea to me. You don't want to have any indicies you don't actually need. They will slow down all inserts and updates. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a Staging and Development DB on the same server?
I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Is there a reason you can't have one instance of the mysql server and simply have foo_staging and foo_development databases? Unless you are tweaking server parameters, this should work just fine... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a Staging and Development DB on the same server?
Jay Paulson wrote: I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Thanks. What's wrong with having a test and production database? Just connect to the proper database when you start your application. mysqladmin create testdata mysqldump production | mysql testdata -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
communication error
The following is an example of an error in our mysql log files: [Warning] Aborted connection 1519045 to db: 'ES_buildings' user: 'esbuildweb' host: 'nrn7.nrcan.gc.ca' (Got an error reading communication packets) Is there some way to determine why/when that happens and to fix our setup so it is corrected? mysql: Ver 14.12 Distrib 5.0.22, for sun-solaris2.8 (sparc) using EditLine wrapper Thanks for any insights. Chris Jones 14 Oneida Avenue Toronto, ON M5J 2E3. Tel. 416-203-7465 Fax. 416-946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joining *3* tables
Hello list I have a large database of contacts, but since not all fields are used I decided to separate all information in 3 tables to save space like this: DB_ADDRESS id int address char(128) ... three columns more ... DB_COMPANY id int company char(64) DB_LISTS id int list char(16) Not all addresses have necesarily a company name, so it is separate in a table DB_COMPANY. Some other addresses are clasified in lists by category and they are grouped in DB_LISTS table. If I want a report with address and company name pairs I use: select address,company from DB_ADDRESS left join DB_COMPANY on DB_ADDRESS.id=DB_COMPANY.id where DB_COMPANY.id is not null; If I want a report of address belonging to a certain category: select address,list from DB_ADDRESS left join DB_LISTS on DB_ADDRESS.id=DB_LISTS.id where DB_LISTS.id is not null and DB_LISTS.list=providers; BUT... If I want to generate a report with address, company and list (category) how can I join the three tables with a single query? or should I first generate a temporal table with the result of the first join and then a second one joining the third table? Thanks for your comments -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joining *3* tables
-Ursprüngliche Nachricht- Von: Renito 73 [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 30. September 2006 04:20 An: mysql@lists.mysql.com Betreff: Joining *3* tables Hello list Hello Mr 73, I have a large database of contacts, but since not all fields are used I decided to separate all information in 3 tables to save space like this: DB_ADDRESS id int address char(128) ... three columns more ... DB_COMPANY id int company char(64) DB_LISTS id int list char(16) Not all addresses have necesarily a company name, so it is separate in a table DB_COMPANY. Some other addresses are clasified in lists by category and they are grouped in DB_LISTS table. Obviously you have a performant server (otherwise you would not accept the performance loss of a join) and are very short on space. So simply use VARCHAR columns, they take up only as much space as their content and are yet faster than a join. If I want a report with address and company name pairs I use: select address,company from DB_ADDRESS left join DB_COMPANY on DB_ADDRESS.id=DB_COMPANY.id where DB_COMPANY.id is not null; If I want a report of address belonging to a certain category: select address,list from DB_ADDRESS left join DB_LISTS on DB_ADDRESS.id=DB_LISTS.id where DB_LISTS.id is not null and DB_LISTS.list=providers; BUT... If I want to generate a report with address, company and list (category) how can I join the three tables with a single query? or should I first generate a temporal table with the result of the first join and then a second one joining the third table? Yes, but remember to drop all indexes from all tables. You can even remove most of the memory from your machine if you want it extra slow. ;-) You can chain as many joins as you want: SELECT * FROM db_address JOIN db_lists USING (id) JOIN db_company USING (id) WHERE ... Thanks for your comments Probably you should read through the MySQL doc once again before you proceed with your project. Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
