RE: myPhpAdmin
Jørn Dahl-Stamnes wrote:
> This is perhaps a bit off-topic, but...
>
> I tried to install and configurate phpAdmin on an
> internal web-server. I copied a default config file and
> created my own config.inc.php file. In this I set
>
> $cfg['Servers'][$i]['host'] =
> 'sql.dahl-stamnes.net';
>
> a 'host sql.dahl-stamnes.net' shows that it is an alias
> pointing to another machine where the mySQL server is
> running.
>
> However, when trying to access myPhpAdmin, I get the
> following error:
>
> Error
>
> MySQL said: Documentation
> #1045 - Access denied for user
> 'quest'@'r2d2.dahl-stamnes.net' (using password: NO)
>
> It seems like the host name given in the config file is
> ignored and that it try to connect to the host where the
> web-server is running.
>
> It should not be like this, should it?
Actually, yes. The error indicates that the user ('quest') you have defined
in your config file, has not been authorized to access the database from
your host 'r2d2.dahl-stamnes.net'
If you have access to a mysql console on the mysql host, try this:
GRANT ALL PRIVILEGES ON .* TO 'quest'@'r2d2.dahl-stamnes.net'
IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
Remember to set the password in the config file accordingly...
Regards,
Peter Normann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Show Constraint_type
Hi everybody, [EMAIL PROTECTED] wrote: > I believe you are looking for the SHOW CREATE TABLE > command. If I am using the MySQL CLI, I prefer the output > formatted with the \G terminator instead of the ; > terminator like this > > SHOW CREATE TABLE tablename\G This is a life saver - I wasn't even aware of the \G terminator. While we are at it, is there any way to cancel a query you just executed? If, for example, you accidently executed a query in the mysql console that would return a million records, is it possible to cancel that query from the console? Pressing ctrl-c would kill the client... Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how do i solve this problem?
Sounds like you are trying to import records already present on server B. You might want to add drop table in your dump... Something like: mysqldump --complete-insert --extended-insert --add-drop-table Peter Normann yousef albarak wrote: > hi, > I am try to restore many databases from server A to > server B,but when with every database i got this error(s) > mysql error: Duplicate entry '65535' for key 1 > > mysql error number: 1062 > > It happen with all databases in different lines. > > Is there any solution for this errors? > > Regards, > Yousef > > _ > Express yourself instantly with MSN Messenger! Download > today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary keys in tables [restarted]
Sorry guys, I just learned that ctrl-return would send the email you are composing, so I got cut off short, so let me try again :-/ It appears to be usual practice from what I can see in this list to use a primary key that actually contain somewhat meaningful data. I have always used primary keys solely for one purpose only: To identify a table row uniquely, using auto incremental integers. Actually, if I were to use a unique number in an application, I would add a column to the table for just that. I would never use the primary key. The table may contain foreign keys and those may hold an entirely different value than this primary - even though the other table would share a one to one relationship. I have always felt, that if you would rely on a primary key for holding somewhat meaningful data, somewhere down the road it could spell trouble. Anyway, I just wanted to hear if anybody would share their thoughts on any advantages or drawbacks as to having the primary key contain meaningful data. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary keys in tables
Hi everybody, It appears to be usual practice from what I can see in this list to use a primary key that actually contain somewhat meaningful data. I have always used primary keys solely for one purpose only: To identify a table row uniquely, using autoincremental integers. Actually, if I were to use a unique number in an application, I would add a column to the table for just that. I would never use the primary key. The table may contain foreign keys and those may hold an entirely different value than this primary - even though the other table would share a one to one relationship. I have always felt, that if you would rely on a primary key for holding somewhat meaningful data, somewhere down the road it could spell trouble. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Install Mysql 4 on Fedora Core 2
Jerry Swanson wrote: > #1. Do I need to uninstall MySQL 3 from the server first? Not really. > #2. Where I can get source for MySQL 4? At your local 7/11. They usually store it hidden behind the counter along with the most popular nuclear launch codes. If they are out of stock, you might consider browsing mysql's site at http://www.mysql.com. Specifically http://dev.mysql.com/downloads/mysql/4.0.html might be interesting. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: > Hi Peter, > If i give the localhost/IP addr, How can i set the > security previleges in my server/system( i have > installed apache server to run the scripts). as i told > earlier i'm using win2000 os, its expecting lot of > securitie prevleges. i can't find those settings. if u > know abt apache server means pls tell something abt > that. It is not the apache server that accesses the mysql server, but the scripting engine of your choice. > I tried to access my system's mysql db and as well as > the scripts in the apache server from other system, > it's asking for the username and password. I didn't > give any username and pwd to my system(i disabled sys > pwd) and also to db. Why it's asking for the username > and pwd. i tried all the username and pwd > combinations. but no result. > If u have any idea pls share with me. When connecting to the mysql server in your script, you must use three parameters: 1. Host name / ip address 2. Username 3. Password I am not familiar with the scripting engine of your choice, but the command to set up a connection to a mysql server would be something like: // Establish connection myConnection = mysql_connect (myHostName, myUserName, myPassword); // Select database on server using connection myDatabase = mysql_select_db( myDatabaseName, myConnection); Check the documentation for the scripting engine for the right syntax and commands. The username is the name of a mysql user etc. Now, consider you have created a database called 'myDatabaseName' you have to set up the user (myUserName) to allow access to this database: 1. Enter the mysql command line client mysql> GRANT ALL PRIVILEGES ON myDatabaseName.* TO 'myUserName'@'HostNameOrIPAddressOfApacheServer' IDENTIFIED BY 'myPassword'; 2. Flush the mysql server user privilege cache mysql> FLUSH PRIVILEGES; Now go read the manual. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: > Sorry. I think i gave some unwanted info in my query. > C-CGI is nothing but - CGI is scripting lang like > JScript and its mainly for web related applications. > we can use this scripting in any languages. i had > chosen "VC++ - Console appln". > In this i used MySQL C-API to achieve the C-MySQL > Connectivity and in my system(win 2000) i'm running > the DB as a service. In my pgm i didn't mention any > host name(I gave null to that). I want to totally > setup this pgm to any one server and after, that pgm > must be independent of my system. for that i want the > clarification. (say the remote server is > www.coolgoose.com). Your script being a client program implies that you have to tell it which host it should be looking for a mysql server on. In other words, if your server is not running on the same host as your program (and even then), you MUST enter a host name or ip address. > Is it neccessary that the mysql > server there and run as a service( In my system, if > the mysql service is stopped means, i can't connect > the DB as well as i can't open 'mysql command line > client'. It is not necessary to run mysql as a service, however it is necessary to run it somehow, that being as a service or standalone application. The command line client is still a client, thus it depends on the mysql server being accessible. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: > Hi Friends, > I'm Ashok. I tried one c-cgi script which is get two > input data from the user (thro' browser) and store it > into MySQL Database, which is running in my m/c as a > service. It's working well in my local pc. > Now i want to host that cgi pgm into one site (where > cgi scriptings r allowed). My doubt is how can i > invoke the database connectivity for that. is it > neccessary that server must have the MySQL DB? > Help me pls. I'm in the dark as to what c-cgi exactly is and thus how you connect to the MySql in the first place, but nevertheless I assume you must be establishing the database connection first hand in your script. This being the case, you should be able to apply a different hostname than 'localhost' in your connection script. Remember to verify that the grant privileges reflect the client not being on localhost. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem when running mysql server
qin lei wrote: > I have instal mysql in /usr/local/mysql. When I run the server. The > message showed on the screen is as follows: [snip] > Warning: Ignoring user change to 'root' because the user was set to > 'mysql' earlier on the command line > 050526 17:46:09 InnoDB: Started > 050526 17:46:09 /usr/local/mysql/bin/mysqld: Can't find file: > './mysql/host.frm' (errno: 13) > 050526 17:46:09 Fatal error: Can't open privilege tables: Can't find > file: './mysql/host.frm' (errno: 13) Did you run the script that install the initial tables : (excerpts from TFM): To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the bin or scripts directory: shell> bin/mysql_install_db --user=mysql shell> scripts/mysql_install_db --user=mysql Peter Normann People fall into 10 categories. Those who understands binary, and those who don't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB to MyISAM
Martijn Tonies <mailto:[EMAIL PROTECTED]> wrote: > Nevertheless, foreign key constraints belong in the database, not in > your application... If you have foreign keys (your wording), you need > foreign key constraints. Period. Plain and simple. No discussion :-) Foreign keys are foreign keys. Constraints are constraints. Foreign key constraints are... well, you do the math. So, in your opinion, MySql was never really a relational database until whatever version enforcing refential constraints was released? Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update combine values
Ismet Dere <mailto:[EMAIL PROTECTED]> wrote: > my question is that, is it possible to combine values of multiple > fields in another field of same table with an update statement such > as this; > > UPDATE UserTable SET FullName = FirstName & MiddleName & LastName; UPDATE UserTable SET FullName = CONCAT(IFNULL(CONCAT(FirstName, ' '), ''), IFNULL(CONCAT(MiddleName, ' '), ''), IFNULL(LastName,'')); Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installer for 4.0.13
Mayuresh Kshirsagar <mailto:[EMAIL PROTECTED]> wrote: > Its the requirement for the project to use 4.0.13. I visites the page > you mentioned. but the only build available there which i can see is > 4.0.24. Am I missing something? Well, without having read the changelogs I can't imagine anything having changed for 4.0.13 to 4.0.24, that would break compatibility with applications. I believe that newer releases in the 4.0.x series only contains bugfixes and maybe minor feature enhancements etc. thus interpreting your requirements for any given project to e.g. mysql 4.0.13 or later... Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help needed to create index
Asha <> wrote: > Is there a physical > limitation in the InnoDb table structure as to why it can't > support FullText indexes? http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installer for 4.0.13
Mayuresh Kshirsagar <mailto:[EMAIL PROTECTED]> wrote: > Where can I find the installer for MYSQL DB 4.0.13 for solaris/linux. > I couldn't find it on the site. Could you guide me please. Is there any reason why you don't want the latest build of version 4.0? If not, you can find them here: http://dev.mysql.com/downloads/mysql/4.0.html Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
Anoop kumar V <mailto:[EMAIL PROTECTED]> wrote: > well - actually it might not be the last 2 days - i just want 2 of > the latest records for every task regardless of what date it is in > the table. Okay, now I think I understand what you need - and if I am correct, this looks like one of the more exotic querys to me, but then again, I'm not like the SQL king around, but let me give it a shot: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec) Latest, MAX(dt_aud_rec) NoSoLatest FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' AND dt_aud_rec = Latest OR dt_aud_rec = NoSoLatest HAVING Latest > NoSoLatest GROUP BY t1.id_secr_rqst ORDER by t1.dt_aud_rec DESC; I am not 100% sure about the syntax, but you might get the idea. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
Anoop kumar V <mailto:[EMAIL PROTECTED]> wrote: > 1. It returns data about only one id_secr_rqst - I want it to return > data about every id_secr_rqst in the table. So, if I understand you correctly (sorry, having a bad day), you want all records for the past two days? Assuming this, you could use something like: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' AND dt_aud_rec > CURDATE() - 2; ORDER by t1.dt_aud_rec DESC; > 2. Limit IMO is mysql specific (I hope I am wrong) is there something > generic so I dont need to bother about which database I am running it > against. As far as I know it is MySql specific. SQL Server's equivalent is SELECT TOP 2 or something. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: complicated query | no Sub query
Hi Anoop Try: SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type='Exception Resource' ORDER by t1.dt_aud_rec DESC LIMIT 2; Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 Joins in 1 Query
shaun thornburgh <mailto:[EMAIL PROTECTED]> wrote: > Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks > but keep the current join on Projects there as well, any ideas? Sorry, Shaun. I misunderstood what you were trying to accomplish first. How about something along these lines (have no way of testing): SELECT W.Number AS WEEK, WEEK(Booking_Start_Date) AS MYWEEK, SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`, SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B` FROM Week W, Bookings B INNER JOIN Projects P USING (Project_ID) WHERE B.Booking_Type = "Booking" AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31' HAVING WEEK = MYWEEK GROUP BY WEEK; Supposing you have 53 records in week with numbers ranging from 1 through 53. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 Joins in 1 Query
shaun thornburgh <mailto:[EMAIL PROTECTED]> wrote: > SELECT WEEK(Booking_Start_Date) AS "WEEK", > SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`, > SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B` > FROM Bookings B INNER JOIN Projects P USING (Project_ID) > WHERE B.Booking_Type = "Booking" > AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01' > AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31' > GROUP BY WEEK; > > The problem with this is that if I have no bookings for week 42 for > example then that week is not shown in the result. To get round this > I created a table called Weeks that contains all the week numbers for > the year. Try a LEFT JOIN: SELECT WEEK(Booking_Start_Date) AS "WEEK", SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`, SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B` FROM Bookings B LEFT JOIN Projects P ON (P.Project_ID = B.Project_ID) WHERE B.Booking_Type = "Booking" AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31' GROUP BY WEEK; Regards Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to get timestamp of inserted record
Hi Jenny, I believe that the timestamp field will automatically be set to NOW() when you INSERT a record... In other words, you can disregard the timestamp field in the INSERT query Peter Normann -Original Message- From: Jenny Christy [mailto:[EMAIL PROTECTED]] Sent: 30. juli 2002 13:11 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: How to get timestamp of inserted record Hello all, suppose I have added one field time_stamp of type timestamp in any existing table. it will show the current timestamp of all records. If i update any record after sometime It will also update the timestamp of updated record with current timestamp. but for insertion of any record...what i have to do...i have to mention the current timestamp value with values of all fields of a record or is there any way to insert new record so that it will automatically take current timestamp for that record. Plz specify the sql query also, if u know right now. Thx in advance. Rgds, Jenny __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.380 / Virus Database: 213 - Release Date: 24-07-2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.380 / Virus Database: 213 - Release Date: 24-07-2002 - 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: Bug in FLOOR() function
I tried the following in 3.23.49: SELECT FLOOR((25*9.54)+0.5); -> 238 SELECT FLOOR(238.5+0.5); -> 239 SELECT FLOOR((25*9.54)+0.50001); -> 239 SELECT FLOOR((25*9.54)+0.51); -> 238 Isn't the world a funny place? Peter Normann -Original Message- From: Andrea Forghieri [mailto:[EMAIL PROTECTED]] Sent: 30. juli 2002 11:51 To: [EMAIL PROTECTED] Subject: Bug in FLOOR() function Dear Sirs, please try this with Mysql 4.0.2 SELECT 25*9.54; -> 238.5 (so far so good) then SELECT (25*9.54)+0.5; -> 239.000 (still OK ) SELECT FLOOR((25*9.54)+0.5); -> 238 (ouch !!) Best Regards Andrea Forghieri Emmegi S.p.A. # Useful info (?) VERSION :MySQL-4.0.2-2.i386.rpm VARIABLES : back_log 50 basedir / binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert OFF connect_timeout 5 datadir /home/mysql/ delay_key_write OFF delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_boolean_syntax + -><()~*:""&| have_bdb NO have_innodb NO have_isam YES have_raid NO have_symlink YES have_openssl NO have_query_cache YES init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 16773120 language /usr/share/mysql/english/ large_files_support ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates OFF log_slow_queries ON long_query_time 10 low_priority_updates OFF lower_case_table_names 0 max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_bulk_insert_tree_size 8388608 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 7168 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file /var/lib/mysql/mandrake.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 rpl_recovery_rank 0 query_buffer_size 0 query_cache_limit 1048576 query_cache_size 0 query_cache_startup_type 1 safe_show_database OFF server_id 1 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /var/lib/mysql/mysql.sock sort_buffer 524280 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone EDT tmp_table_size 33554432 tmpdir /home/mysql/tmp/ version 4.0.2-alpha-log wait_timeout 28800 - 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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.380 / Virus Database: 213 - Release Date: 24-07-2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.380 / Virus Database: 213 - Release Date: 24-07-2002 - 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: Need help with a complex query
I don't know if it would be possible to use something like this, but maybe it's worth a try... SELECT Customers.*, SUM(IF(IFNULL(Orders_Items.productid, 0)='23',1,0)) BINGO FROM Customers LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && (Ordered_Items.productid = '23')) WHERE Customers.id=Subscriptions.customerid AND Subscriptions.listid='2' AND Ordered_Items.orderid IS NULL AND Orders.customerid IS NULL AND NOT Customers.bad_email GROUP BY Customer.email HAVING BINGO = 0; Jeez either I should be getting some coffee or some sleep Peter Normann -Original Message- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 18. juni 2002 22:21 To: Peter Normann; [EMAIL PROTECTED] Subject: Re: Need help with a complex query >>> Try - and I mean *try* :-) >>> >>> SELECT Customers.* FROM Customers, Subscriptions >>> LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) >>> LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && >>> (Ordered_Items.productid = '23')) >>> WHERE Customers.id=Subscriptions.customerid >>> AND Subscriptions.listid='2' >>> AND Ordered_Items.orderid IS NULL >>> AND Orders.customerid IS NULL >>> AND NOT Customers.bad_email; >> >> Well, MySQL has been chugging on this one for about 15 minutes now, and >> it's >> still going. So I'm not sure if it is a problem with the query or the >> optimization of my MySQL databases. There are 30,000+ records in the >> Customers database, but the query should have finished by now, right? >> > Well, my best guess is that you should consider indexing your tables... > If you haven't done so already, indexing will cause dramatic effect on > the speed of the queries... > > Peter Normann Okay. I've indexed all fields involved, and I've gotten the query time down to under 3 seconds! However, I've discovered a flaw in the query. A customer can order multiple times. The above query still includes customers that have ordered product #23, but have also ordered other products on separate orders. Example: Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 0 | | 1001 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 23| | 2 | 1001 | | 2 | 98| +++ +-+---+ Customer #1001 would be included in the found set of the query above. He would be removed because he ordered product #23 on order #1, but he would also be included because he didn't order product #23 on order #2. Any ideas on how to exclude these multi-order customers? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.371 / Virus Database: 206 - Release Date: 13-06-2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.371 / Virus Database: 206 - Release Date: 13-06-2002 - 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: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
Sorry, I forgot: SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode=weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS' GROUP BY weather_data.Site; -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:38 To: [EMAIL PROTECTED] Subject: Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one mysql> SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS -> Temp, weather_locations.Name AS Name -> FROM weather_locations -> LEFT JOIN weather_data ON weather_locations.MetarCode = -> weather_data.Site -> WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR -> weather_data.Site='FAJS'; ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP colum ns is illegal if there is no GROUP BY clause mysql> Hmmm, I wonder what the manual can tell me about group by... I'll have a look and see what I can come up with. If you know what's missing, I wont mind finding out :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Chris Knipe'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, June 16, 2002 7:34 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one > Does > > SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS > Temp, weather_locations.Name AS Name > FROM weather_locations > LEFT JOIN weather_data ON weather_locations.MetarCode = > weather_data.Site > WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR > weather_data.Site='FAJS'; > > Work? > > You can add LIMIT 1 to the query... > > Peter Normann > > -Original Message- > From: Chris Knipe [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 19:00 > To: [EMAIL PROTECTED] > Subject: Re: LEFT JOIN again... Although, this time I think it's a > design flaw rather than a query one > > > Ok, > > It seems I spoke to soon here... > > SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name > FROM weather_locations > LEFT JOIN weather_data ON weather_locations.MetarCode = > weather_data.Site > WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR > weather_data.Site='FAJS'; > > That, does what I want... However, there's a catch now, that I became > aware > of after the duplicated data started filling the weather_data table. > > mysql> SELECT * FROM weather_data WHERE Site='FACT'; > +-++ > > -+--+---+---+--+--+ > | EntryID | RawData| Date > | > Site | WindSpeed | WindDirection | Temp | DewPoint | > +-++ > > -+--+---+---+--+--+ > | 5 | FACT 161200Z 31008KT FEW030 17/11 Q1021 NOSIG | > 2002-06-16 > 12:00:00 | FACT |19 | Northwest | 17 | 11 | > | 36 | FACT 161300Z 31009KT FEW030 16/10 Q1021 NOSIG | > 2002-06-16 > 13:00:00 | FACT |21 | Northwest | 16 | 10 | > | 64 | FACT 161400Z 30007KT FEW030 16/11 Q1020 NOSIG | > 2002-06-16 > 14:00:00 | FACT |16 | Northwest | 16 | 11 | > | 73 | FACT 161500Z 30007KT FEW030 15/11 Q1020 NOSIG | > 2002-06-16 > 15:00:00 | FACT |16 | Northwest | 15 | 11 | > +-++ > > -+--+---+---+--+--+ > 4 rows in set (0.13 sec) > > The problem now, is that the above query will return all four records > for > the FACT site as specified in my WHERE clause. Is there a way that I > can > add to that query, the funcionailty to: > > -- Order by reverse Date, and show only 1 record for every site > specified by > the WHERE Clause (i.e., only get the newest data for each site I > specified). > OR > -- Use a SELECT DISTINCT so that I can have distinct SITE values in the > JOIN > query... > > Thanks... > > > > > - Original Message - > From: "Peter Normann
RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
Does SELECT weather_data.Site, weather_data.Temp, MAX(weather_data.Date) AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; Work? You can add LIMIT 1 to the query... Peter Normann -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:00 To: [EMAIL PROTECTED] Subject: Re: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Ok, It seems I spoke to soon here... SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; That, does what I want... However, there's a catch now, that I became aware of after the duplicated data started filling the weather_data table. mysql> SELECT * FROM weather_data WHERE Site='FACT'; +-++ -+--+---+---+--+--+ | EntryID | RawData| Date | Site | WindSpeed | WindDirection | Temp | DewPoint | +-++ -+--+---+---+--+--+ | 5 | FACT 161200Z 31008KT FEW030 17/11 Q1021 NOSIG | 2002-06-16 12:00:00 | FACT |19 | Northwest | 17 | 11 | | 36 | FACT 161300Z 31009KT FEW030 16/10 Q1021 NOSIG | 2002-06-16 13:00:00 | FACT |21 | Northwest | 16 | 10 | | 64 | FACT 161400Z 30007KT FEW030 16/11 Q1020 NOSIG | 2002-06-16 14:00:00 | FACT |16 | Northwest | 16 | 11 | | 73 | FACT 161500Z 30007KT FEW030 15/11 Q1020 NOSIG | 2002-06-16 15:00:00 | FACT |16 | Northwest | 15 | 11 | +-++ -+--+---+---+--+--+ 4 rows in set (0.13 sec) The problem now, is that the above query will return all four records for the FACT site as specified in my WHERE clause. Is there a way that I can add to that query, the funcionailty to: -- Order by reverse Date, and show only 1 record for every site specified by the WHERE Clause (i.e., only get the newest data for each site I specified). OR -- Use a SELECT DISTINCT so that I can have distinct SITE values in the JOIN query... Thanks... - Original Message - From: "Peter Normann" <[EMAIL PROTECTED]> To: "'Chris Knipe'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, June 16, 2002 5:44 PM Subject: RE: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one > Sorry, I didn't read the mail through before replying... :-/ > > -Original Message- > From: Chris Knipe [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 15:43 > To: [EMAIL PROTECTED] > Subject: LEFT JOIN again... Although, this time I think it's a design > flaw rather than a query one > > > Hiya again, > > Ok, based on all the info, feedback and help I got the last time, I was > able to construct the following query: > > mysql> SELECT weather_data.Temp, > ->weather_locations.Name > -> FROM weather_data > -> LEFT JOIN weather_data ON > -> weather_locations.MetarCode = weather_data.Site > -> WHERE weather_locations.MetarCode IS NULL; > ERROR 1066: Not unique table/alias: 'weather_data' > > The tables look as follows: > mysql> DESCRIBE weather_data; > +---+--+--+-+-+- > +---+--+--+-+-+ > ---+ > | Field | Type | Null | Key | Default | > Extra > | > +---+--+--+-+-+- > +---+--+--+-+-+ > ---+ > | EntryID | bigint(20) | | PRI | NULL| > auto_increment | > | RawData | varchar(250) | | MUL | | | > | Date | datetime | | MUL | -00-00 00:00:00 | | > | Site | varchar(4) | | MUL | | | > | WindSpeed | tinyint(4) | YES | MUL | NULL| | > | WindDirection | varchar(250) | YES | MUL | NULL| | > | Temp | tinyint(4) | YES | MUL | NULL
RE: Need help with a complex query
Well, my best guess is that you should consider indexing your tables... If you haven't done so already, indexing will cause dramatic effect on the speed of the queries... Peter Normann -Original Message- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:18 To: Peter Normann; [EMAIL PROTECTED] Subject: Re: Need help with a complex query > Try - and I mean *try* :-) > > SELECT Customers.* FROM Customers, Subscriptions > LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) > LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && > (Ordered_Items.productid = '23')) > WHERE Customers.id=Subscriptions.customerid > AND Subscriptions.listid='2' > AND Ordered_Items.orderid IS NULL > AND Orders.customerid IS NULL > AND NOT Customers.bad_email; Well, MySQL has been chugging on this one for about 15 minutes now, and it's still going. So I'm not sure if it is a problem with the query or the optimization of my MySQL databases. There are 30,000+ records in the Customers database, but the query should have finished by now, right? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.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 - 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: A LEFT OUTER JOIN question.
You're welcome, Ian. Being new to this list, I'm just trying to gather enough credit to get people to look at my recent post ;-) Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:29 To: Peter Normann; [EMAIL PROTECTED] Subject: RE: A LEFT OUTER JOIN question. Hi Peter, Thanks a lot - that does the trick! Incredibly quick response :) Ian > -Original Message- > From: Peter Normann [mailto:[EMAIL PROTECTED]] > Sent: 16 June 2002 17:06 > To: 'mySQL list'; [EMAIL PROTECTED] > Subject: RE: A LEFT OUTER JOIN question. > > > Try > > SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON > (Items.ItemID = Stuff.ItemID && (type=0 || type IS NULL)); > > > Peter Normann > > -Original Message- > From: mySQL list [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 17:59 > To: [EMAIL PROTECTED] > Subject: A LEFT OUTER JOIN question. > > > I have two tables, 'items' and 'stuff'. > > Items has a primary key of itemid. and looksmlike this > > ItemID other fields... > > 1 ... > 2 > 3 > 4 > 5 > 6 > > Stuff contains something like this: > > ItemID type info > 1 0blah blah > 2 0something > 2 1... > 3 1... > > I want a query which returns all the items, and if it exists, the info > field from related 'stuff' of type 0. > > So far I've got: > > SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON > Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); > > This works if there are no Stuff records with a type other than 0, eg, > the last one in the example. The Query returns items 1,2,4,5, & 6, no > 3. > > How can I make a query which returns all items and type 0's info (if > it exists)? > > ian > > > - > 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: Need help with a complex query
Try - and I mean *try* :-) SELECT Customers.* FROM Customers, Subscriptions LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && (Ordered_Items.productid = '23')) WHERE Customers.id=Subscriptions.customerid AND Subscriptions.listid='2' AND Ordered_Items.orderid IS NULL AND Orders.customerid IS NULL AND NOT Customers.bad_email; Peter Normann -Original Message- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:23 To: [EMAIL PROTECTED] Subject: Need help with a complex query I am currently a web designer who's taught himself the necessary basics of PHP and MySQL. I've reached a point where I can't figure out a complex query. I have a mailing list that customers can sign up for if they want to receive "Special Offer" mailings when products go on sale. So when it's time to send out a mailing on a sale product, I need to select all customers who have signed up for list #2. However, (and here's the big problem) I need to filter out the customers that have bad email addresses marked in their customer record, *and* I need to filter out the customers who have already purchased the sale product. Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 1 | | 1001 | 2 | | 1002 | 0 | | 1002 | 1 | | 1003 | 0 | | 1003 | 2 | | 1004 | 0 | | 1004 | 2 | | 1005 | 0 | | 1005 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 24| | 2 | 1002 | | 2 | 98| | 3 | 1003 | | 3 | 23| | 4 | 1004 | | 4 | 56| +++ +-+---+ For example: I want to send out a mailing on product #23 A) Customer 1001 has a bad email address, so he shouldn't be selected B) Customer 1002 has a good email address, but he's not signed up for list #2, so he shouldn't be selected C) Customer 1003 has a good email address, he's signed up for list #2, but he's ordered product #23 before, so he shouldn't be selected D) Customer 1004 has a good email address, he's signed up for list #2, and he's ordered a product that isn't #23, so he *should* be selected E) Customer 1005 has a good email address, he's signed up for list #2, and he's never ordered, so he *should* be selected Summary: > Not Selected: 1001,1002,1003 > Selected: 1004,1005 Can anyone create a query that will return the results I need. Keep in mind that I am far from being a MySQL expert, so if you start using too much technical specifics, my head might explode. ;) Thanks in advance! Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ Success .http://www.success.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 - 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: Nested queries and joins
Oops, I meant SELECT ad_catego.* FROM ad_catego, db_subcategory WHERE ad_catego.db_subcategory=rules.db_subcategory AND rules.db_category_int != '2' AND rules.db_login !='$session_login'; ? Peter Normann -Original Message- From: Peter Normann [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:15 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Nested queries and joins SELECT ad_catego.* FROM ad_catego, db_subcategory WHERE ad_catego.db_subcategory=rules.db_subcategory AND rules.db_category != '2' AND rules.db_login !='$session_login'; ? Peter Normann -Original Message- From: Balteo [mailto:[EMAIL PROTECTED]] Sent: 17. juni 2002 00:12 To: [EMAIL PROTECTED] Subject: Nested queries and joins Hello, I have the following nested query that I would like to port to Mysql: SELECT * FROM ad_catego WHERE db_subcategory NOT IN (SELECT rules.db_subcategory FROM rules WHERE rules.db_login='$session_login') AND db_category_int =2 The query uses two tables desc ad_catego; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | db_subcategory | varchar(30) | | PRI | | | | db_category| varchar(15) | | | | | | db_category_int| smallint(6) | | | 0 | | | db_subcategory_int | smallint(6) | | | 0 | | ++-+--+-+-+---+ desc rules; ++-+--+-++---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++---+ | db_login | varchar(15) | | PRI || | | db_subcategory | varchar(30) | | PRI || | | db_daterule| date| | | -00-00 | | ++-+--+-++---+ I tried several joins without success... I would like to select all the subcategories that a user does not have. Can anyone help? Thanks in advance, Balteo. - 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: Nested queries and joins
SELECT ad_catego.* FROM ad_catego, db_subcategory WHERE ad_catego.db_subcategory=rules.db_subcategory AND rules.db_category != '2' AND rules.db_login !='$session_login'; ? Peter Normann -Original Message- From: Balteo [mailto:[EMAIL PROTECTED]] Sent: 17. juni 2002 00:12 To: [EMAIL PROTECTED] Subject: Nested queries and joins Hello, I have the following nested query that I would like to port to Mysql: SELECT * FROM ad_catego WHERE db_subcategory NOT IN (SELECT rules.db_subcategory FROM rules WHERE rules.db_login='$session_login') AND db_category_int =2 The query uses two tables desc ad_catego; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | db_subcategory | varchar(30) | | PRI | | | | db_category| varchar(15) | | | | | | db_category_int| smallint(6) | | | 0 | | | db_subcategory_int | smallint(6) | | | 0 | | ++-+--+-+-+---+ desc rules; ++-+--+-++---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++---+ | db_login | varchar(15) | | PRI || | | db_subcategory | varchar(30) | | PRI || | | db_daterule| date| | | -00-00 | | ++-+--+-++---+ I tried several joins without success... I would like to select all the subcategories that a user does not have. Can anyone help? Thanks in advance, Balteo. - 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: A LEFT OUTER JOIN question.
Try SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON (Items.ItemID = Stuff.ItemID && (type=0 || type IS NULL)); Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 17:59 To: [EMAIL PROTECTED] Subject: A LEFT OUTER JOIN question. I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, & 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - 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: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
Sorry, I didn't read the mail through before replying... :-/ -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 15:43 To: [EMAIL PROTECTED] Subject: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Hiya again, Ok, based on all the info, feedback and help I got the last time, I was able to construct the following query: mysql> SELECT weather_data.Temp, ->weather_locations.Name -> FROM weather_data -> LEFT JOIN weather_data ON -> weather_locations.MetarCode = weather_data.Site -> WHERE weather_locations.MetarCode IS NULL; ERROR 1066: Not unique table/alias: 'weather_data' The tables look as follows: mysql> DESCRIBE weather_data; +---+--+--+-+-+- +---+--+--+-+-+ ---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+- +---+--+--+-+-+ ---+ | EntryID | bigint(20) | | PRI | NULL| auto_increment | | RawData | varchar(250) | | MUL | | | | Date | datetime | | MUL | -00-00 00:00:00 | | | Site | varchar(4) | | MUL | | | | WindSpeed | tinyint(4) | YES | MUL | NULL| | | WindDirection | varchar(250) | YES | MUL | NULL| | | Temp | tinyint(4) | YES | MUL | NULL| | | DewPoint | tinyint(4) | YES | MUL | NULL| | +---+--+--+-+-+- +---+--+--+-+-+ ---+ 8 rows in set (0.29 sec) mysql> DESCRIBE weather_locations; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | LocationID | tinyint(4) | | PRI | NULL| auto_increment | | MetarCode | varchar(4) | | UNI | || | Name | varchar(250) | | UNI | || | Province | varchar(250) | YES | MUL | NULL|| ++--+--+-+-++ 4 rows in set (0.03 sec) The basic background It's used to compile a history of weather information on approximately 75 locations in my country (ala .za). The weather_locations table, holds unique rows describing the various locations of the weather stations. In this table, MetarCode is a 4 character key identifing the weather station, while Name represents the physical location (such as the Town or airport). In my weather_data table, I save the actual weather data. I cannot use UNIQUE keys in this table, because I need to build up a history of the weather changes. In this table, Site represents the same weather station identifier as MetarCode in the weather_locations table. So, I'm trying to accomplish my JOIN on weather_locations.MetarCode and weather_data.Site. Basically, the data that I want to have returned: SELECT weather_locations.Name, weather_data.Temp FROM weather_data WHERE weather_data.Site='FACT'; Yes, this is a broken query, but I think it would give a good idea of what I want I want the weather site's name from the weather_locations table, plus whatever column I want from the weather_data table (such as temprature, WindSpeeds, WindDirection, etc). Bah... While typing this and playing arround some more, I got it working SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; Thanks anyways :-) - 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: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one....
I think you need to use the following query: mysql> SELECT weather_data.Temp, ->weather_locations.Name -> FROM weather_data -> LEFT JOIN weather_locations ON -> weather_locations.MetarCode = weather_data.Site -> WHERE weather_locations.MetarCode IS NULL; Note the LEFT JOIN weather_locations in stead of weather_data Peter Normann -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 15:43 To: [EMAIL PROTECTED] Subject: LEFT JOIN again... Although, this time I think it's a design flaw rather than a query one Hiya again, Ok, based on all the info, feedback and help I got the last time, I was able to construct the following query: mysql> SELECT weather_data.Temp, ->weather_locations.Name -> FROM weather_data -> LEFT JOIN weather_data ON -> weather_locations.MetarCode = weather_data.Site -> WHERE weather_locations.MetarCode IS NULL; ERROR 1066: Not unique table/alias: 'weather_data' The tables look as follows: mysql> DESCRIBE weather_data; +---+--+--+-+-+- +---+--+--+-+-+ ---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+- +---+--+--+-+-+ ---+ | EntryID | bigint(20) | | PRI | NULL| auto_increment | | RawData | varchar(250) | | MUL | | | | Date | datetime | | MUL | -00-00 00:00:00 | | | Site | varchar(4) | | MUL | | | | WindSpeed | tinyint(4) | YES | MUL | NULL| | | WindDirection | varchar(250) | YES | MUL | NULL| | | Temp | tinyint(4) | YES | MUL | NULL| | | DewPoint | tinyint(4) | YES | MUL | NULL| | +---+--+--+-+-+- +---+--+--+-+-+ ---+ 8 rows in set (0.29 sec) mysql> DESCRIBE weather_locations; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | LocationID | tinyint(4) | | PRI | NULL| auto_increment | | MetarCode | varchar(4) | | UNI | || | Name | varchar(250) | | UNI | || | Province | varchar(250) | YES | MUL | NULL|| ++--+--+-+-++ 4 rows in set (0.03 sec) The basic background It's used to compile a history of weather information on approximately 75 locations in my country (ala .za). The weather_locations table, holds unique rows describing the various locations of the weather stations. In this table, MetarCode is a 4 character key identifing the weather station, while Name represents the physical location (such as the Town or airport). In my weather_data table, I save the actual weather data. I cannot use UNIQUE keys in this table, because I need to build up a history of the weather changes. In this table, Site represents the same weather station identifier as MetarCode in the weather_locations table. So, I'm trying to accomplish my JOIN on weather_locations.MetarCode and weather_data.Site. Basically, the data that I want to have returned: SELECT weather_locations.Name, weather_data.Temp FROM weather_data WHERE weather_data.Site='FACT'; Yes, this is a broken query, but I think it would give a good idea of what I want I want the weather site's name from the weather_locations table, plus whatever column I want from the weather_data table (such as temprature, WindSpeeds, WindDirection, etc). Bah... While typing this and playing arround some more, I got it working SELECT weather_data.Temp AS Temp, weather_locations.Name AS Name FROM weather_locations LEFT JOIN weather_data ON weather_locations.MetarCode = weather_data.Site WHERE weather_data.Site='FACT' OR weather_data.Site='FADN' OR weather_data.Site='FAJS'; Thanks anyways :-) - 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 - Be
RE: Help with indexing
Sorry for not mentioning it, but yes I have indexed manager.Person... Peter Normann -Original Message- From: httpd [mailto:httpd] On Behalf Of Joseph Bueno Sent: 16. juni 2002 10:54 To: Peter Normann Cc: [EMAIL PROTECTED] Subject: Re: Help with indexing Peter Normann wrote : > > Hello, > > I have a query I hope somebody can help me optimize. I have two tables > (given example): > > CREATE TABLE area ( > Area_ID INTEGER UNSIGNED, > Master INTEGER UNSIGNED > ); > > CREATE TABLE manager ( > Manager_ID INTEGER UNSIGNED, > Person INTEGER UNSIGNED, > AreaINTEGER UNSIGNED > ); > > I have an organization, that has three levels. The top level area has > Master IS NULL, the second and third level having Master reference > their parent area. Lets call area level 1 = area, level 2 = team, > level 3 = group. > > Suppose I have a manager for an area (level 1) and I would like to get > all areas that he manages a long with all subareas (teams and group) > that belongs to his areas. I go > > SELECT distinct area.Area_ID Area_ID > FROM manager manager, area area, area team, area group > WHERE manager.Person='1' AND > (manager.Area = area.Area_ID || > (manager.Area=team.Area_ID && team.Area_ID=area.Master) || > (manager.Area=group.Area_ID && group.Area_ID=team.Master && > team.Area_ID=area.Master)); > > This query is painfully slow. I guess it produces millions and > millions of table scans having only a couple of 157 area records. > > I have tried to make al sorts of indexes with Area_ID, Master as > single and multi-column indexes etc... When I do a > > mysql> explain SELECT distinct area.Area_ID Area_ID FROM manager > manager, area area, area team, area hold WHERE manager.Person='1' AND > manager.Area = area.Area_ID || (manager.Area=team.Area_ID && > team.Area_ID=area.Master) || (manager.Area=hold.Area_ID && > hold.Area_ID=team.Master && team.Area_ID=area.Master); > +-+---+++-+--+-- > +-+ > | table | type | possible_keys | key| key_len | ref | > rows | Extra | > +-+---+++-+--+-- > +-+ > | area| index | PRIMARY,area_index | areamaster | 11 | NULL | > 157 | Using index; Using temporary| > | hold| index | PRIMARY,area_index | PRIMARY| 4 | NULL | > 157 | Using index; Distinct | > | team| index | PRIMARY,area_index | areamaster | 11 | NULL | > 157 | Using index; Distinct | > | manager | ALL | manager_area | NULL |NULL | NULL | > 186 | range checked for each record (index map: 16); Distinct | > +-+---+--------++-+--+-- > +-+ > > Any help will be appreciated, since I use this query in a number of > functions and it is a big problem... > > Best regards > > Peter Normann > Hi, Since you didn't show us how you index these tables, just a silly question: have you an index on manager(person) ? Regards -- Joseph Bueno NetClub/Trader.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 - 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
Help with indexing
Hello, I have a query I hope somebody can help me optimize. I have two tables (given example): CREATE TABLE area ( Area_ID INTEGER UNSIGNED, Master INTEGER UNSIGNED ); CREATE TABLE manager ( Manager_ID INTEGER UNSIGNED, Person INTEGER UNSIGNED, AreaINTEGER UNSIGNED ); I have an organization, that has three levels. The top level area has Master IS NULL, the second and third level having Master reference their parent area. Lets call area level 1 = area, level 2 = team, level 3 = group. Suppose I have a manager for an area (level 1) and I would like to get all areas that he manages a long with all subareas (teams and group) that belongs to his areas. I go SELECT distinct area.Area_ID Area_ID FROM manager manager, area area, area team, area group WHERE manager.Person='1' AND (manager.Area = area.Area_ID || (manager.Area=team.Area_ID && team.Area_ID=area.Master) || (manager.Area=group.Area_ID && group.Area_ID=team.Master && team.Area_ID=area.Master)); This query is painfully slow. I guess it produces millions and millions of table scans having only a couple of 157 area records. I have tried to make al sorts of indexes with Area_ID, Master as single and multi-column indexes etc... When I do a mysql> explain SELECT distinct area.Area_ID Area_ID FROM manager manager, area area, area team, area hold WHERE manager.Person='1' AND manager.Area = area.Area_ID || (manager.Area=team.Area_ID && team.Area_ID=area.Master) || (manager.Area=hold.Area_ID && hold.Area_ID=team.Master && team.Area_ID=area.Master); +-+---+++-+--+-- +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+---+++-+--+-- +-+ | area| index | PRIMARY,area_index | areamaster | 11 | NULL | 157 | Using index; Using temporary| | hold| index | PRIMARY,area_index | PRIMARY| 4 | NULL | 157 | Using index; Distinct | | team| index | PRIMARY,area_index | areamaster | 11 | NULL | 157 | Using index; Distinct | | manager | ALL | manager_area | NULL |NULL | NULL | 186 | range checked for each record (index map: 16); Distinct | +-+---+++-+--+-- +-+ Any help will be appreciated, since I use this query in a number of functions and it is a big problem... Best regards Peter Normann - 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
