Re: Join query returning duplicate entries
Hi, sorry i tried to help but i hardly understand the use of join in your query since the joined table is not used anywhere. From: Trimurthy trimur...@tulassi.com To: mysql@lists.mysql.com Sent: Thursday, 4 April 2013, 14:21 Subject: Join query returning duplicate entries Hi list, i wrote the following query and it is returning duplicate entries as shown below, can any one suggest me how to avoid this duplicate entries, with out using distinct. Query: select p.date,p.coacode,p.type,p.crdr,p.quantity,p.amount from ac_financialpostings p join (select iac from ims_itemcodes where (cat = 'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date Output: ++-++--+--++ | date | coacode | type | crdr | quantity | amount | ++-++--+--++ | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0 | | 2012-11-10 | 600500 |
Re: MyISAM better than innodb for large files?
Which one is more suitable for developing an ERP Application ? MyISAM or InnoDB
?
Are there other tools to backup MySQL Database than Standard GUI Tools which
MySQL provide in the website ? So far, I use this GUI tools and setup an
automatic backup on 9AM everyday. Is this backup tools is reliable enough ?
Regards,
Lucky.
From: mos mo...@fastmail.fm
To: mysql@lists.mysql.com
Sent: Fri, April 9, 2010 10:03:26 PM
Subject: Re: MyISAM better than innodb for large files?
Kyong,
Thanks for the feedback on InnoDb. I will tinker with it when I have
more time. I wonder if MySQL will ever release an alternative to Innodb
like Falcon or whether Falcon is dead as a dodo? :-)
Mike
At 11:07 PM 4/8/2010, Kyong Kim wrote:
We've seen good results throwing more RAM to the buffer pool.
It is true that InnoDB data never gets accessed directly on disk.
The only downside I know of with a larger buffer pool is slower restarts.
The load speed depends on the order of the inserts.
Random inserts or updates to primary key will cause result in very
poor performance.
I once ran a test doing completely random insert to InnoDB with a very
small buffer pool on my VM dev machine and it took days to load a
million rows before finally failing. Keep in mind that there may have
been other factors at work as well (we had a rather unusual indexing
strategy which worked for our use case). If you can pre-sort your load
file by primary key order, your load speed should be much better.
In terms of loading data, I doubt you will see better performance with
InnoDB than MyISAM. Our selection was heavily biased towards data
access. I have heard that InnoDB insert buffer scales much more
linearly than MyISAM but I don't know the details. We clustered our
data using a longer composite primary key and saw fairly good data
access performance.
I would caution against InnoDB if you foresee heavy random inserts.
Kyong
On Thu, Apr 8, 2010 at 8:21 AM, mos mo...@fastmail.fm wrote:
At 09:10 PM 4/7/2010, you wrote:
Also depends on your data access pattern as well.
If you can take advantage of clustering my primary key for your
selects, then InnoDB could do it for you.
My suggestion would be to write some queries based on projected
workload, build 2 tables with lots and lots of data, and do some
isolated testing. For work, I do a lot of query profiling using
maatkit. Be sure to clear out as much of the caching as possible
including the OS cache.
In a related topic, does anyone know how well InnoDb is going to perform if
you have a 250 million row table (100gb) and only 8gb of RAM? It was my
understanding that InnoDb needed to fit as much of the table into memory as
it could for it to be fast. Also, how long is it going to take to load 250
million rows (using Load Data InFile) compared to a MyISAM table? I've
always found InnoDb to be incredibly slow at loading large amounts of data
and nothing I could think of would speed things up. I too would like to
switch to InnoDb but until I can solve these problem I'm sticking with
MyISAM for large tables.
Mike
On Mon, Apr 5, 2010 at 7:25 AM, Jan Steinman j...@bytesmiths.com wrote:
From: Gavin Towey gto...@ffn.com
InnoDB should be your default for all tables, unless you have specific
requirements that need myisam. One specific example of an appropriate
task
for myisam is where you need very high insert throughput, and you're
not
doing any updates/deletes concurrently.
A couple other things: InnoDB does relations better, MyISAM does search
of
text fields.
If we can control fuel we can control the masses; if we can control food
we
can control individuals. -- Henry Kissinger
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=kykim...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=kykim...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=luckyx_cool_...@yahoo.com
Re: Selecting Dates
Just trying to help. SELECT * FROM orders WHERE order_date BETWEEN '2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date = '2010-01-01' AND = '2010-01-30' ORDER BY order_date; From: ML mailingli...@mailnewsrss.com To: mysql@lists.mysql.com Sent: Mon, February 1, 2010 8:35:01 AM Subject: Selecting Dates Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Neither of these work. What am I missing? -ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=luckyx_cool_...@yahoo.com
How to change mysql default database directory
Hi all, I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). I want to know if there's a way to change MySQL default database directory from C: to D:. Thanks.
Re: Trigger problem
Yes, the trigger code is works. Many thanks !! Now I understand the use of delimiter command. Thanks again... =) My next question is, do we able to view the triggers that has been created ? And how ? David Schneider-Joseph [EMAIL PROTECTED] wrote: My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Trigger problem
Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Trigger problem
No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph [EMAIL PROTECTED] wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
