Tricky Join with Animal-DB
Hi SQL-Community I need once more your help, I have a tricky problem with my animal-db. I have a table with animals and a table with animal-pics. I have a third table. In this table you can see, which pic belongs to which animal: CREATE TABLE animal ( id int(11) NOT NULL auto_increment, animal varchar(250) NULL, PRIMARY KEY (id) ); CREATE TABLE pic ( id int(11) NOT NULL auto_increment, pic varchar(250) NULL, PRIMARY KEY (id) ); CREATE TABLE animal_pic ( animalid int(11) NULL , picid int(11) NULL ); The problem is now, that there can be n pics for an animal! With which join can I show the animals with all pics for this animal (even if there are more than one pic for an animal)? Thank you for you ideas! Martin
How to select every second record
Hi SQL-Users Is it possible to select only every second record from a record set? I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ... Can this be done with LIMIT? Thank you! Greetings, Martin
Multiple table count in one sql-query
Hello People I should have the count from 3 tables in one query; something like this: select count(*) from table1 union select count(*) from table2 union select count(*) from table3 union; The Problem ist, that the query gives 3 results back (the count from table1, table2 and table3) BUT I should have only one result, the added value from all 3 tables... How can I do this within MySQL? Are there variables I could use within the query? Other possibilities? Thank you for your help! Kind Regards, Martin Rytz
Suppress the 0 value
Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? Thank you in advance, Martin Rytz
LOAD DATA LOCAL: The used command is not allowed with this MySQL version
Hi mysql-community! I have a problem with mysql 3.23.49a: If I try to use the statement 'LOAD DATA LOCAL INFILE '/storage/hosting/uxclients/www.xxx.ch/transfer/golfclub.csv' INTO TABLE test' mysql replys: 'The used command is not allowed with this MySQL version'. Why this error-message? Was LOAD DATA LOCAL not supported with 3.23? I read, that LOAD DATA LOCAL was added with 3.22.6 Do you have an idea? Thank you, Martin
Count Rows within unions
Hi SQL-Cracks How can I count the rows within a select with multiple unions? Count rows with one select is easy: select count(*) from table Count rows over multiple tables is complicated: select name from table1 union select name from table2 union select name from table3 order by name How can I count the rows over this 3 tables? Thank you in advance! Martin Rytz
Problem with 4.1.3 that I not had with 4.0.20
Good Morning Community! I have a problem with 4.1.3 that I not had with 4.0.20. Here is my table: CREATE TABLE url_cat_copy ( id int(11) NOT NULL auto_increment, url varchar(100) default NULL, domain varchar(100) default NULL, bytes bigint(20) default NULL, hits bigint(20) default NULL, bytes_priv decimal(20,2) default NULL, hits_priv decimal(20,2) default NULL, bytes_bus decimal(20,2) default NULL, hits_bus decimal(20,2) default NULL, cat1 varchar(100) default NULL, cat2 varchar(100) default NULL, cat3 varchar(100) default NULL, PRIMARY KEY (id) ); Here is a sample-data: INSERT INTO url_cat_copy VALUES (272, '195.120.225.10', '195.120.225.10', 3040, 3, 2533.33, 2.50, 506.67, 0.50, 'Shopping', 'Religion', 'Music/Webradio'); Here ist the query: SELECT * FROM url_cat_copy WHERE bytes - ( bytes_priv + bytes_bus ) <> 0 OR hits - (hits_priv + hits_bus) <> 0; The result from the query should be no record (empty recordset). With 4.0.20 it was, with 4.1.3 it is not. Can you reconstruct the problem on your 4.1.3 installation? Why has the result changed? How I must change the query, that the result is an empty recordset again? Thank you in advance! Martin Rytz
Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Hi All I am very confused about MySQL 4.1.3. I have problems with the character set and the collation. Under 4.0.20 I had never such problems. I downloaded the beta-files from version 4.1.3b and zipped it to c:\mysql. Then I loaded my data within a script with "load data infile" into my tables... The point is, that all tables have now the collation 'latin1_swedish_ci'?!!? Is this the default collation? Why does this collation appear? Interesting is, that the meta-data (all tables in the mysql-db) does have the 'latin1_swedish_ci' - collation too. The problem is now with phpmyadmin, if I make a select like "SUBSTRING_INDEX( domain, '.', -2 ) from url_cat", the following error-message appears: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE). I found out, that if I use the following statement "select SUBSTRING_INDEX( domain, _latin1'.', -2 ) from url_cat" it works But the Problem is, i won't change all my old scripts from 4.0!!! I found out, that if I use mysql in DOS, everything works without error!!! Do I have to change something in phpmyadmin (i have version 2.6.0rc1)? I am very confused about it and i don't know how and which character-set and collation I have to choose. I live in Switzerland... Which character-set and collation I have to choose? Is there a 'standard' character-set/collation I should use? Do I have to set parameters in the my.cnf - file? My system runs under Windows XP with SP2. Thank you in advance for your help. Yours Martin Rytz DBA from Switzerland
Problem with Slow Update Query
Hi all I have a problem with slow update queries like these (5 examples): update url_cat set domain = '01net' where left( domain, instr( domain, '.' ) -1 ) = '01net'; update url_cat set domain = '1-meta' where left( domain, instr( domain, '.' ) -1 ) = '1-meta'; update url_cat set domain = '105' where left( domain, instr( domain, '.' ) -1 ) = '105'; update url_cat set domain = '123love' where left( domain, instr( domain, '.' ) -1 ) = '123love'; update url_cat set domain = 'google' where left( domain, instr( domain, '.' ) -1 ) = 'google'; before the update, the field domain contains 'google.com' or 'google.de' or 'google.ch' and after the update it contains only 'google'. i have to make thousends of this updates an it takes a long time. the table 'url_cat' contains about 100'000 entries! an index would help, but mysql does not use any static index. the index should contain the where-condition... but this is not possible, because the lenght of the condition differs?! does anybody have another idea? thank you in advance. martin
