Re: Join query returning duplicate entries
- Original Message - From: Lucky Wijaya luckyx_cool_...@yahoo.com To: mysql@lists.mysql.com Sent: Thursday, 4 April, 2013 10:51:50 AM Subject: 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. Of course it's used - the joined subquery limits the items to male or female birds. As for an answer to your question, Trimurthy, just use SELECT DISTINCT rest of your query. The cause of the duplicates may be that the iac for male and female birds is identical. -- Linux Kriek Wanderung April 19-21, 2013 http://www.tuxera.be/lkw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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: Join query returning duplicate entries
Hello Trimurthy,
On 4/4/2013 3:21 AM, Trimurthy wrote:
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
Some more options to the DISTINCT clause may be either EXISTS or IN()
Examples:
select ...
from
ac_financialpostings p
WHERE
exists (select 2 from ims_itemcodes where (cat = 'Male Birds' or cat
= 'Female Birds') and ims_itemcodes.iac = p.coacode)
AND p.trnum like '%02'
AND p.date between '2012-10-04' and '2013-04-04'
order by date
select ...
from ac_financialpostings p
WHERE
p.coacode IN(select iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds'))
AND p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Or you can use the DISTINCT clause in your subquery, too
select ...
from ac_financialpostings p
join (select DISTINCT 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
Or you can use an explicit temporary table
CREATE TEMPORARY TABLE tmp_iac (key(iac)) ENGINE=MEMORY
SELECT DISTINCT iac
FROM ims_itemcodes
WHERE cat IN('Male Birds', 'Female Birds');
SELECT ...
FROM ac_finanancialpositions p
JOIN tmp_iac
ON tmp_iac.iac = p.coacode
WHERE ...
ORDER BY ... ;
DROP TEMPORARY TABLE tmp_iac;
The advantage to this is that before MySQL 5.6, the implicit temporary
table created by your subquery was not indexed. For more than a trivial
number of rows to compare against, that can reduce overall performance
because the results of your subquery would need to be scanned for each
row of the outer table in the main query it was joining to. Based on the
WHERE clause, all rows from the outer table may not be in the 'join set'
so this is not always a Cartesian product problem.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Determing number of queries
I am looking to spec out hardware for a new database server. I figured a good starting point would be to find out how much usage my current server is getting. It just a local machine that runs mysql and is queried by a few users here in the office. Is there a way that mysql can tell me info about it's workload? Thanks Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Determing number of queries
2013/4/4 Richard Reina gatorre...@gmail.com I am looking to spec out hardware for a new database server. I figured a good starting point would be to find out how much usage my current server is getting. It just a local machine that runs mysql and is queried by a few users here in the office. Is there a way that mysql can tell me info about it's workload? Thanks Hello, You can start with show innodb status; For the basics, you might want to start graphing the stats under ROW OPERATIONS (inserts, updates, reads, deletes...) Manuel.
error-log aging
Is there somewhere within MySQL means of aging the error log, that it not indefinitly grow big, or is that done through the OS and filesystem on which mysqld runs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Determing number of queries
2013/04/04 22:40 +0200, Manuel Arostegui You can start with show innodb status; It is now show engine innodb status -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error-log aging
2013/04/04 23:18 +0200, Reindl Harald Is there somewhere within MySQL means of aging the error log, that it not indefinitly grow big, or is that done through the OS and filesystem on which mysqld runs? man logrotate Not Unix! In any case, I take this to mean that this is not done within MySQL, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Determing number of queries
2013/4/4 h...@tbbs.net 2013/04/04 22:40 +0200, Manuel Arostegui You can start with show innodb status; It is now show engine innodb status Yep, sorry, not used to it just yet :-) -- Manuel Aróstegui Systems Team tuenti.com
MySQL on RHEL4
Hi, We're trying to upgrade our existing MySQL 5.1.26 to MySQL 5.1.68 but the installation gives error: libc.so.6(GLIBC_2.4) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 rtld(GNU_HASH) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 Now, GLIBS2.4 is not available for RHEL4 and MySQL 5.1 and 5.5 are supported on RHEL4 as per this: http://www.mysql.com/support/supportedplatforms/database.html Any ideas? Regards, Nitin
Re: MySQL on RHEL4
On 2013-04-05, Nitin Mehta ntn...@yahoo.com wrote: We're trying to upgrade our existing MySQL 5.1.26 to MySQL 5.1.68 but the installation gives error:libc.so.6(GLIBC_2.4) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 rtld(GNU_HASH) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 Now, GLIBS2.4 is not available for RHEL4 and MySQL 5.1 and 5.5 are supported on RHEL4 as per this: http://www.mysql.com/support/supportedplatforms/database.html Any ideas? You have apparently taken an RPM for RHEL5 and attempted to use it in RHEL4. It should not be a surprise that it doesn't work. To get it working, either use the correct RPMs (which may not be available), compile from source yourself, or upgrade to RHEL5. --keith -- kkel...@wombat.san-francisco.ca.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
