SQL Query Help
Can someone please help me with the following? Normally I would do this with a nested select, but since this is not available in MySQL I think I need help. Here is what I have: An order table with sales tax total and an orderdetail table with ordered, itemid and qty. What I need to do is form a sql query that will allow me to pull get the tax amount on all orders where product id is 1, 2 or 3 for example. The problem that I have is when I do a straight join on select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Is this making sense? David McInnis - 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: Query help
Here is my table schema. Thanks for your help. David ORDERS +--+--- | Field| Type +--+--- | id | int(10) unsigned zerofill | clientid | int(11) | refid| int(11) | sessionid| varchar(40) | staffid | int(11) | staffcomp| date | auth_code| varchar(6) | trans_id | varchar(30) | dt_trans | datetime | job_target | smallint(6) | exp_level| smallint(6) | careerchg| char(1) | subtotal | float(10,2) | tax | float(10,2) | rushfee | float(10,2) | total| float(10,2) | dt_create| datetime | dt_timestamp | datetime | dt_update| date +--+-- ORDERDETAIL ++--- | Field | Type ++--- | id | int(10) unsigned zerofill | orderid| int(11) | itemid | varchar(10) | qty| int(11) | unitcost | float(10,2) | extended | float(10,2) | grp_id | varchar(10) | grp_pwd| varchar(10) ++--- -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 1:00 PM To: '[EMAIL PROTECTED]' Subject: Re: Query help David McInnis wrote: select tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I can get multiple tax amounts where an order has multiple matching records in orderdetail. I know that I can group by order.id, but what I eventually need to do is pull sum(tax) and not just tax. Post the basic schema for the database tables in question and it would help. It doesn't sound like you need a subselect, but that may just be a misinterpretation. PS, I'd like some stats from the mysql list admins as to how many messages a day are blocked because of the requirement to include SQL or QUERY in the message and how many of those are _actually_ spam ... just watch for one day ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: Query help
Well, what I need to do is something like this: select sum(tax) from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) The problem is this that when I run this sql query: select orderdetail.itemid, orderdetail.id as odid, orders.id, orders.tax from orders, orderdetail where orders.id = orderdetail.orderid and (productid = 1 or productid = 2 or productid = 3) I get something like this: Itemid OdidOrderid Tax 1 13 1 16.71 2 14 1 16.71 3 15 1 16.71 1 16 2 10.00 1 17 3 15.00 Which leads me to believe that if I take a sum(tax) it will sum all three. I only want one record, not all three. David -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 2:19 PM To: David McInnis Cc: [EMAIL PROTECTED] Subject: Re: Query help So you want something like: SELECT ORDERDETAIL.id as detailid, qty, unitcost, unitcost * qty as extended from ORDERDETAIL LEFT JOIN ORDERS ON orderid = ORDERS.id; Right? What's the problem you have with tax requests that you hinted at last time? David McInnis wrote: ORDERS +--+--- | Field| Type +--+--- | id | int(10) unsigned zerofill | subtotal | float(10,2) | tax | float(10,2) | rushfee | float(10,2) | total| float(10,2) ORDERDETAIL ++--- | Field | Type ++--- | id | int(10) unsigned zerofill | orderid| int(11) | qty| int(11) | unitcost | float(10,2) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
Mysql Replication
I am having a problem with replication. I had a master / slave environment set up and running successfully. I then took the slave offline for a while and made substantial changes to the master. Then I master copied the new database environment to the slave server by taring the tables and copying them to the slave server. Then I restarted the slave and now my slave database is not working. Is there something that I need to do in order to resync the slave with the new master? David McInnis Not really an SQL or query question, but ... - 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
Getting difference between two datatime values
The following code does not work because time_to_sec() works on the 24
hour clock and cannot work between 2 days. For example a session
started at 11:59 PM will not carry forward until 12:01 AM based on this
logic.
Does anyone have a better solution for me?
David McInnis
$sql = select *, (time_to_sec(now()) - time_to_sec(dt_expire))
as elapsed from sessions_client where sessionid = '$cp_sessionid';
$result = @mysql_query($sql, $connection)
or die ('Database Error - Could not query
sessions_client.');
$numrows = mysql_num_rows($result);
if ($numrows == 1) {
$data = mysql_fetch_array($result);
$clientid = $data[clientid];
$dt_lastaccess = $data[dt_lastaccess];
$elapsed= $data[elapsed];
if ($elapsed 0) {
// validate session
// this is where the logic fails around midnight
}
} else {
// expired session
}
SQL and Query
-
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
Too Many Connections
MySQL keeps locking up (I get a Too many connections error.) Is there a way that I can increase the number of connections that MySQL will take? SQL and Query David McInnis - 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: backup databases
Could you share your script for doing the date thing with the rest of us? That sounds useful. David McInnis -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 10:40 AM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: backup databases I regularly back up all my databases with mysqldump: mysqldump -uUsername -pPassword --all-databases tmp.sql tar -cf MySQL-Backup-DATE.tar.gz -z tmp.sql rm -f tmp.sql I use a script (to determine DATE) in conjunction with cron to back all databases up nightly, and I also have the script e-mail the .tar.gz file to me so I can have a backup in case the server goes down. I've heard of hotcopy but never used it, so I couldn't recommend or unrecommend it. - Jonahtan -Original Message- From: Kory Wheatley [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 10:27 AM To: [EMAIL PROTECTED] Subject: backup databases What is the best method to backup all your MYSQL DATBASES with not a lot of down time. I have read about using mysqlhotcopy and mysqldump are these the best methods. Also what procedure is followed in backing up the databases. I'm basically looking for the must accurate and reliable way of doing this. -- # Kory Wheatley Academic Computing Analyst Sr. Phone 282-3874 # Everything must point to him. - 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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Insert from select
I think that I saw a similar query last week some time but I cannot remember the sql syntax. Is it possible to insert from a select. Something like insert into table_a from select * from table_b where blah=somevalue Thanks, David McInnis - 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: MYSQL HELP
How would you use a temporary table? Is that the most efficient way? David -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 11:03 AM To: Shade, Richard; [EMAIL PROTECTED] Subject: Re: MYSQL HELP Richard, Does anyone know what is wrong with this syntax... Select H.FinishCode, L.RowID From CallHistory As H, BD2.CallList As L Where (H.CallTime in(Select MAX(CallTime) From CallHistory Where H.RowID = L.RowID) AND H.RowID = L.RowID); Sub-selects not (yet) permitted by MySQL (RTFM: 1.7.4.1 Sub-SELECTs) Looks like a job for two SELECTs and a temporary table... Regards, =dn - 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: urgent problem with mysql and phpmyadmin
My guess. . . I have seen this problem with other databases . . . don't know if it is true for MySQL as well. Try to change to your field definition to float(5,2) as it may be counting the (.) as part of your field size. So if you number is xx.xx you will need 5,2. 4,2 would give you xx.x David McInnis -Original Message- From: Andrew Bunkell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 5:31 PM To: [EMAIL PROTECTED] Subject: urgent problem with mysql and phpmyadmin I have created a table in the mysql db, using phpmyadmin with the following statement: create table books ( isbn char(13) not null,author char(30),title(60),price float(4,2),primary key(isbn)); which works fine and creates the table i need the problem comes when i try to insert some values into the price column using the phpmyadmin form, if for example i try to enter the price 34.99 it will only allow me to enter 34.9, and then when i go to the browse feature it shows the value as 34.90 any ideas on what's going wrong??? any help would be much apprecited. thanks - 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
InnoDM or ISAM??
I have run MySQL for the last 2 years using the default database tables. Has anyone made the migration to InnoDB? Is it stable? What are the drawbacks of using InnoDB? David McInnis sql - 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: ignoring in ORDER
You may be able to sort using a regex expression. David -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 1:39 AM To: 'abdul - ÚÈÏ ÇáÑÍãä' [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: ignoring in ORDER Hi Abdul, Can Mysql ignore alphabet , like 'the' when ORDERING a name. A sample : I have a table (first_name) : - The test. - Falcon - The Armageddon - Battle When ordering it will be : Battle Falcon The Armageddon The test. Can it be like this : The Armageddon Battle Falcon The test. Is that possible? =Anything is possible! =This is a common 'problem' in the library world where in the context of titles 'the', 'a', 'al' are considered 'noise-words'. Some of the 'library' scripts available on the various support/tutorial sites may offer assistance. =Here are three choices: 1 regimentation: insist that all title data entered, is expressed in a sequence-friendly fashion, eg Armageddon, The; 2 smart data: have two title fields in your db. One which is the title straight off the cover. The other which is a 'sequencing field' - sometimes with noise words removed/words re-ordered, sometimes exactly the same. Users should be presented with the former, but the RDBMS is programmed to use/sequence using the latter; 3 smart queries: (this is more of a challenge) look up IF() [6.3 Functions for Use in SELECT and WHERE Clauses] and rewrite the query so that if the title commences with a noise word miss it out of the sort process, eg if the left three characters are the sequence using only the Arrmageddon part. =Regards, =dn - 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: download MySQLdump?
It is part of the standard distributions. If you installed from any of the packaged distributions, you probably already have it. I know that it installed for me when I installed on both Linux and WindowsXP. David A McInnis Managing Editor, PRWEB -- PRWeb.com - The Free Newswire. Free online press release since 1997. -Original Message- From: Tjeerd van Beek [mailto:[EMAIL PROTECTED]] Sent: Friday, February 01, 2002 6:22 AM To: MySQL Subject: download MySQLdump? Where can I download mysqldump? Tjeerd van Beek - 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
