Re: Trouble with LEFT JOIN
Peter, Thank you very much for your reply. Three weeks later I am realizing that the 'NOT IN' solution I had above does not work after all. Accordingly, I went back and looked at your solution and it is close but it only gives NULL results I am looking for something that excludes challenges that have are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying. This may be unsolvable. 2015-09-04 12:30 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > On 2015-09-04 11:39 AM, Richard Reina wrote: > > > 2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > >> On 2015-09-04 9:40 AM, Richard Reina wrote: >> >>> I have the following two tables; >>> >>> mysql> select * from challenge; >>> +++-++--+-+--+ >>> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | >>> +++-++--+-+--+ >>> | 1 | 1 | 1 | Fall | 2015 | >>> 1175 >>> | >>> | 2 | 1 | 4 | Fall | 2015 | >>> 1175 >>> | >>> | 3 | 1 | 3 | Fall | 2015 | >>> 1175 >>> | >>> | 4 | 1 |10 | Fall | 2015 | >>> 1175 | >>> | 5 | 1 |13 | Fall | 2015 | >>> 1175 | >>> | 6 | 1 | 2 | Fall | 2015 | >>> 1175 >>> | >>> ++--+--++---+-+ >>> 6 rows in set (0.00 sec) >>> >>> mysql> select * from patrocinio; >>> +++---+-+ >>> | ID | PTRN_ID | CHLNG_ID | AMOUNT | >>> +++---+-+ >>> | 1 | 1 | 1 | 1.00 | >>> | 2 | 4 | 3 | 2.00 | >>> | 3 | 3 | 6 | 1.00 | >>> ++---+-----++ >>> I would like to select all rows from challenges which are NOT linked to a >>> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of >>> challenges. >>> >>> I am trying to go about this with a LEFT JOIN query but it does not seem >>> to >>> be working for me. >>> >>> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON >>> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; >>> >> >> ... where p.chlng_id IS NULL; >> >> (Look up exclusion joins) >> >> PB >> >> - >> >> > > Hi Peter, > > Thanks for the reply. Along those lines I have also tried: > > select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE > p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL; > > But that's not working either. > > > drop table if exists patrocinio, challenge; > create table challenge( > id smallint,plr_id smallint,acc_type_id smallint, > season char(4), year year, char_id smallint ); > insert into challenge values > ( 1 , 1 , 1 , 'Fall' , 2015 , > 1175), > ( 2 , 1 , 4 , 'Fall' , 2015 , > 1175), > ( 3 , 1 , 3 , 'Fall' , 2015 , > 1175), > ( 4 , 1 , 10 , 'Fall' , 2015 , > 1175 ), > ( 5 , 1 , 13 , 'Fall' , 2015 , > 1175 ), > ( 6 , 1 , 2 , 'Fall' , 2015 , > 1175); > create table patrocinio( > id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) ); > insert into patrocinio values > ( 1 , 1 , 1 , 1.00 ), > ( 2 , 4 , 3 , 2.00 ), > ( 3 , 3 , 6 , 1.00 ); > > select c.id , p.chlng_id > from challenge c > left join patrocinio p on c.id=p.chlng_id ; > +--+--+ > | id | chlng_id | > +--+--+ > |1 |1 | > |3 |3 | > |6 |6 | > |2 | NULL | > |4 | NULL | > |5 | NULL | > +--+--+ > > select c.id , p.chlng_id > from challenge c > left join patrocinio p on c.id=p.chlng_id > where p.chlng_id is null; > +--+--+ > | id | chlng_id | > +--+--+ > |2 | NULL | > |4 | NULL | > |5 | NULL | > +--+--+ > > PB > > > > > >
RE: Trouble with LEFT JOIN
Hi There, If I interpreted what you are trying to do is to return all the rows from Challenge and reference Patrocinio excluding those rows in Patrocinio where PTRN_ID is 1? Not sure if the below is possible in MySQL but I've used this in other places when doing a left join and needing to exclude specific rows when satisfying the join condition. select ... from table_1 t1 left join table_2 t2 on t1.id = t2.id and not t2.fld = 1 where ... Apologies I don't have a MySQL instance to test the above and it's been quite a while since I've dabbled seriously with this database. HTH Cheers. AB -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Saturday, 26 September 2015 1:09 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: Trouble with LEFT JOIN Peter, Thank you very much for your reply. Three weeks later I am realizing that the 'NOT IN' solution I had above does not work after all. Accordingly, I went back and looked at your solution and it is close but it only gives NULL results I am looking for something that excludes challenges that have are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying. This may be unsolvable. 2015-09-04 12:30 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > On 2015-09-04 11:39 AM, Richard Reina wrote: > > > 2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > >> On 2015-09-04 9:40 AM, Richard Reina wrote: >> >>> I have the following two tables; >>> >>> mysql> select * from challenge; >>> +++-++--+-+--+ >>> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | >>> +++-++--+-+--+ >>> | 1 | 1 | 1 | Fall | 2015 | >>> 1175 >>> | >>> | 2 | 1 | 4 | Fall | 2015 | >>> 1175 >>> | >>> | 3 | 1 | 3 | Fall | 2015 | >>> 1175 >>> | >>> | 4 | 1 |10 | Fall | 2015 | >>> 1175 | >>> | 5 | 1 |13 | Fall | 2015 | >>> 1175 | >>> | 6 | 1 | 2 | Fall | 2015 | >>> 1175 >>> | >>> ++--+--++---+-+ >>> 6 rows in set (0.00 sec) >>> >>> mysql> select * from patrocinio; >>> +++---+-+ >>> | ID | PTRN_ID | CHLNG_ID | AMOUNT | >>> +++---+-+ >>> | 1 | 1 | 1 | 1.00 | >>> | 2 | 4 | 3 | 2.00 | >>> | 3 | 3 | 6 | 1.00 | >>> ++---+-----++ >>> I would like to select all rows from challenges which are NOT linked to a >>> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of >>> challenges. >>> >>> I am trying to go about this with a LEFT JOIN query but it does not seem >>> to >>> be working for me. >>> >>> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON >>> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; >>> >> >> ... where p.chlng_id IS NULL; >> >> (Look up exclusion joins) >> >> PB >> >> - >> >> > > Hi Peter, > > Thanks for the reply. Along those lines I have also tried: > > select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE > p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL; > > But that's not working either. > > > drop table if exists patrocinio, challenge; > create table challenge( > id smallint,plr_id smallint,acc_type_id smallint, > season char(4), year year, char_id smallint ); > insert into challenge values > ( 1 , 1 , 1 , 'Fall' , 2015 , > 1175), > ( 2 , 1 , 4 , 'Fall' , 2015 , > 1175), > ( 3 , 1 , 3 , 'Fall' , 2015 , > 1175), > ( 4 , 1 , 10 , 'Fall' , 2015 , > 1175 ), > ( 5 , 1 , 13 , 'Fall' , 2015 , > 1175 ), > ( 6 , 1 , 2 , 'Fall' , 2015 , > 1175); > create table patrocinio( > id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) ); > insert into patrocinio values > ( 1 , 1 , 1
Trouble with LEFT JOIN
I have the following two tables; mysql> select * from challenge; +++-++--+-+--+ | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | +++-++--+-+--+ | 1 | 1 | 1 | Fall | 2015 | 1175 | | 2 | 1 | 4 | Fall | 2015 | 1175 | | 3 | 1 | 3 | Fall | 2015 | 1175 | | 4 | 1 |10 | Fall | 2015 | 1175 | | 5 | 1 |13 | Fall | 2015 | 1175 | | 6 | 1 | 2 | Fall | 2015 | 1175 | ++--+--++---+-+ 6 rows in set (0.00 sec) mysql> select * from patrocinio; +++---+-+ | ID | PTRN_ID | CHLNG_ID | AMOUNT | +++---+-+ | 1 | 1 | 1 | 1.00 | | 2 | 4 | 3 | 2.00 | | 3 | 3 | 6 | 1.00 | ++---+-++ I would like to select all rows from challenges which are NOT linked to a patrocinio with the PTRN_ID -- which would be rows 2 through 6 of challenges. I am trying to go about this with a LEFT JOIN query but it does not seem to be working for me. mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; Empty set (0.00 sec) Instead of getting rows 2 through 6 of challenges I get no rows. Any help on how to correctly do this query would be greatly appreciated. Thanks
Re: Trouble with LEFT JOIN
On 2015-09-04 9:40 AM, Richard Reina wrote: I have the following two tables; mysql> select * from challenge; +++-++--+-+--+ | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | +++-++--+-+--+ | 1 | 1 | 1 | Fall | 2015 | 1175 | | 2 | 1 | 4 | Fall | 2015 | 1175 | | 3 | 1 | 3 | Fall | 2015 | 1175 | | 4 | 1 |10 | Fall | 2015 | 1175 | | 5 | 1 |13 | Fall | 2015 | 1175 | | 6 | 1 | 2 | Fall | 2015 | 1175 | ++--+--++---+-+ 6 rows in set (0.00 sec) mysql> select * from patrocinio; +++---+-+ | ID | PTRN_ID | CHLNG_ID | AMOUNT | +++---+-+ | 1 | 1 | 1 | 1.00 | | 2 | 4 | 3 | 2.00 | | 3 | 3 | 6 | 1.00 | ++---+-++ I would like to select all rows from challenges which are NOT linked to a patrocinio with the PTRN_ID -- which would be rows 2 through 6 of challenges. I am trying to go about this with a LEFT JOIN query but it does not seem to be working for me. mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; ... where p.chlng_id IS NULL; (Look up exclusion joins) PB - Empty set (0.00 sec) Instead of getting rows 2 through 6 of challenges I get no rows. Any help on how to correctly do this query would be greatly appreciated. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Trouble with LEFT JOIN
2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > On 2015-09-04 9:40 AM, Richard Reina wrote: > >> I have the following two tables; >> >> mysql> select * from challenge; >> +++-++--+-+--+ >> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | >> +++-++--+-+--+ >> | 1 | 1 | 1 | Fall | 2015 | >> 1175 >> | >> | 2 | 1 | 4 | Fall | 2015 | >> 1175 >> | >> | 3 | 1 | 3 | Fall | 2015 | >> 1175 >> | >> | 4 | 1 |10 | Fall | 2015 | >> 1175 | >> | 5 | 1 |13 | Fall | 2015 | >> 1175 | >> | 6 | 1 | 2 | Fall | 2015 | >> 1175 >> | >> ++--+--++---+-+ >> 6 rows in set (0.00 sec) >> >> mysql> select * from patrocinio; >> +++---+-+ >> | ID | PTRN_ID | CHLNG_ID | AMOUNT | >> +++---+-+ >> | 1 | 1 | 1 | 1.00 | >> | 2 | 4 | 3 | 2.00 | >> | 3 | 3 | 6 | 1.00 | >> ++---+-+----+ >> I would like to select all rows from challenges which are NOT linked to a >> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of >> challenges. >> >> I am trying to go about this with a LEFT JOIN query but it does not seem >> to >> be working for me. >> >> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON >> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; >> > > ... where p.chlng_id IS NULL; > > (Look up exclusion joins) > > PB > > - > > > Empty set (0.00 sec) >> >> Instead of getting rows 2 through 6 of challenges I get no rows. >> >> Any help on how to correctly do this query would be greatly appreciated. >> >> Thanks >> >> > Got it to work this way: SELECT c.ID AS ID FROM challenge c WHERE c.ID NOT IN ( SELECT ID from patrocinio p WHERE p.PTRN_ID=1 );
Re: Trouble with LEFT JOIN
On 2015-09-04 11:39 AM, Richard Reina wrote: 2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net <mailto:peter.braw...@earthlink.net>>: On 2015-09-04 9:40 AM, Richard Reina wrote: I have the following two tables; mysql> select * from challenge; +++-++--+-+--+ | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | +++-++--+-+--+ | 1 | 1 | 1 | Fall | 2015 | 1175 | | 2 | 1 | 4 | Fall | 2015 | 1175 | | 3 | 1 | 3 | Fall | 2015 | 1175 | | 4 | 1 |10 | Fall | 2015 | 1175 | | 5 | 1 |13 | Fall | 2015 | 1175 | | 6 | 1 | 2 | Fall | 2015 | 1175 | ++--+--++---+-+ 6 rows in set (0.00 sec) mysql> select * from patrocinio; +++---+-+ | ID | PTRN_ID | CHLNG_ID | AMOUNT | +++---+-+ | 1 | 1 | 1 | 1.00 | | 2 | 4 | 3 | 2.00 | | 3 | 3 | 6 | 1.00 | ++---+-++ I would like to select all rows from challenges which are NOT linked to a patrocinio with the PTRN_ID -- which would be rows 2 through 6 of challenges. I am trying to go about this with a LEFT JOIN query but it does not seem to be working for me. mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; ... where p.chlng_id IS NULL; (Look up exclusion joins) PB - Hi Peter, Thanks for the reply. Along those lines I have also tried: select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL; But that's not working either. drop table if exists patrocinio, challenge; create table challenge( id smallint,plr_id smallint,acc_type_id smallint, season char(4), year year, char_id smallint ); insert into challenge values ( 1 , 1 , 1 , 'Fall' , 2015 , 1175), ( 2 , 1 , 4 , 'Fall' , 2015 , 1175), ( 3 , 1 , 3 , 'Fall' , 2015 , 1175), ( 4 , 1 , 10 , 'Fall' , 2015 , 1175 ), ( 5 , 1 , 13 , 'Fall' , 2015 , 1175 ), ( 6 , 1 , 2 , 'Fall' , 2015 , 1175); create table patrocinio( id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) ); insert into patrocinio values ( 1 , 1 , 1 , 1.00 ), ( 2 , 4 , 3 , 2.00 ), ( 3 , 3 , 6 , 1.00 ); select c.id , p.chlng_id from challenge c left join patrocinio p on c.id=p.chlng_id ; +--+--+ | id | chlng_id | +--+--+ |1 |1 | |3 |3 | |6 |6 | |2 | NULL | |4 | NULL | |5 | NULL | +--+--+ select c.id , p.chlng_id from challenge c left join patrocinio p on c.id=p.chlng_id where p.chlng_id is null; +--+--+ | id | chlng_id | +--+--+ |2 | NULL | |4 | NULL | |5 | NULL | +--+--+ PB
Re: Why does a group_concat on a join change aggregate values?
On 2015/05/07 19:42, Paul Halliday wrote: Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp I don't see why this worries you. Joining often increases variation. Indeed, if in some case an inner join never did, maybe the joined tables are needlessly separate. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Why does a group_concat on a join change aggregate values?
Hi Paul, On 5/7/2015 10:17 AM, Paul Halliday wrote: Fighting a bit with this one... If I do something like (pseudo): SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1 returns something like: n c_types 1 t9 when I add a left join though: SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types, GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id returns something like: val c_types d_types 3t9,t9,t9 a2,a3,a9 I can have as many group_concats against the same table with varying results and they don't affect COUNT() but once I do that JOIN things start to fall apart. What is happening behind the scenes? Thanks! Here's a simple test. Change the query to no longer have the aggregate functions, then start counting rows by hand. SELECT val AS n , types AS c_types , two.types AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id The other thing that springs to mind is that you lack a GROUP BY in your query. It isn't required but they can often help get you to the correct answer. Best regards, -- Shawn Green MySQL Senior 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
Re: Why does a group_concat on a join change aggregate values?
Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp On Thu, May 7, 2015 at 4:11 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Paul, On 5/7/2015 10:17 AM, Paul Halliday wrote: Fighting a bit with this one... If I do something like (pseudo): SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1 returns something like: n c_types 1 t9 when I add a left join though: SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types, GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id returns something like: val c_types d_types 3t9,t9,t9 a2,a3,a9 I can have as many group_concats against the same table with varying results and they don't affect COUNT() but once I do that JOIN things start to fall apart. What is happening behind the scenes? Thanks! Here's a simple test. Change the query to no longer have the aggregate functions, then start counting rows by hand. SELECT val AS n , types AS c_types , two.types AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id The other thing that springs to mind is that you lack a GROUP BY in your query. It isn't required but they can often help get you to the correct answer. Best regards, -- Shawn Green MySQL Senior 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 -- Paul Halliday http://www.pintumbler.org/
Why does a group_concat on a join change aggregate values?
Fighting a bit with this one... If I do something like (pseudo): SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types FROM tbl1 returns something like: n c_types 1 t9 when I add a left join though: SELECT count(val) AS n, GROUP_CONCAT(types) AS c_types, GROUP_CONCAT(two.types) AS d_types FROM tbl1 LEFT JOIN tbl2 AS two ON tbl1.id = tbl2.id returns something like: val c_types d_types 3t9,t9,t9 a2,a3,a9 I can have as many group_concats against the same table with varying results and they don't affect COUNT() but once I do that JOIN things start to fall apart. What is happening behind the scenes? Thanks! -- Paul Halliday http://www.pintumbler.org/
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
Re: Help with left outer join
On 12/11/2012 7:22 PM, h...@tbbs.net wrote: ... (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... I admit I am a control freak when it comes to the accuracy of my queries, the integrity of my data, and the ease of maintenance for my SQL statements. Because of this, I much prefer the regular predictability of the ANSI JOIN syntax (demonstrated above) for any JOIN except for an intentional Cartesian product. For that case alone, I prefer a comma join (Cartesian product example) SELECT ... FROM table1, table2 ... Not only is the ANSI syntax the only way to specify an OUTER join (such as LEFT JOIN or RIGHT JOIN) it forces the author of the statement to recognize that they do or do not have an ON clause for this table relationship. Separating the same conditions into a WHERE clause makes it very easy to overlook a table relationship and accidentally create a Cartesian product which can often devastate query performance. It is perfectly legal to use the comma-join syntax with MySQL but I strongly recommend against it just because it can only be used for INNER joins or CROSS joins and because it forces you to put your relationship conditions in the WHERE clause. Another strike (to me, anyway) against the comma join is that in order to process joins more like the specifications in the SQL standards, we demoted the precedence of the comma operator with 5.0.12. These changes also affected the behavior of the USING and NATURAL JOIN operators. http://dev.mysql.com/doc/refman/5.5/en/join.html Regards, -- 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
Re: Help with left outer join
On Wed, Dec 12, 2012 at 8:25 AM, Shawn Green shawn.l.gr...@oracle.com wrote: On 12/11/2012 7:22 PM, h...@tbbs.net wrote: ... (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... I admit I am a control freak when it comes to the accuracy of my queries, the integrity of my data, and the ease of maintenance for my SQL statements. Because of this, I much prefer the regular predictability of the ANSI JOIN syntax (demonstrated above) for any JOIN except for an intentional Cartesian product. For that case alone, I prefer a comma join (Cartesian product example) SELECT ... FROM table1, table2 ... Not only is the ANSI syntax the only way to specify an OUTER join (such as LEFT JOIN or RIGHT JOIN) it forces the author of the statement to recognize that they do or do not have an ON clause for this table relationship. Separating the same conditions into a WHERE clause makes it very easy to overlook a table relationship and accidentally create a Cartesian product which can often devastate query performance. It is perfectly legal to use the comma-join syntax with MySQL but I strongly recommend against it just because it can only be used for INNER joins or CROSS joins and because it forces you to put your relationship conditions in the WHERE clause. Another strike (to me, anyway) against the comma join is that in order to process joins more like the specifications in the SQL standards, we demoted the precedence of the comma operator with 5.0.12. These changes also affected the behavior of the USING and NATURAL JOIN operators. http://dev.mysql.com/doc/refman/5.5/en/join.html Thanks very much Shawn for this very informative post. I learned SQL in the early 1980's and I was not taught the JOIN syntax, only the comma join and WHERE. It's really just force of habit that I write queries that way. I will try very hard in the future to break that habit and use the JOIN syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
On Tue, Dec 11, 2012 at 8:48 PM, Peter Brawley peter.braw...@earthlink.net wrote: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. Then a bit of reordering is required ... SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Your query worked exactly like my original one - i.e. when data_cst.image_measurer_id is NULL I don't get that data_cst row. But I changed the RIGHT JOIN to a LEFT JOIN and then it was doing exactly what I wanted. Thanks for all the help! On 2012-12-11 5:11 PM, Larry Martell wrote: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Thanks very much for the reply. This is giving me: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
On Tue, Dec 11, 2012 at 7:22 PM, h...@tbbs.net wrote: 2012/12/11 16:19 -0500, Larry Martell I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? Modern forms do not give a left join if one uses WHERE-clause to reduce a full cross-join to an inner join. It is better to start with something like this, FROM data_cst JOIN data_target ON data_target.id = data_cst.target_name_id JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id JOIN (data_category JOIN data_tool ON data_category.id = data_tool.category_id) ON data_tool.id = data_cst.tool_id but I am not too sure where to bracket data_tool. When you have put it into a 'FROM'-clause with 'JOIN', not comma, separating the tables, with the same outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'. Maybe you want it between data_cstimage and data_measparams. (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) I'm not familiar with the USING clause. I'll have to look into that. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
On 2012-12-11 3:19 PM, Larry Martell wrote: I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. Explicit join syntax makes such queries easier to read, write, develop debug. Is this what you mean? SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst *RIGHT JOIN data_cstimage*ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; PB http://www.artfulsoftware.com I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? TIA! -larry
Re: Help with left outer join
On Tue, Dec 11, 2012 at 5:12 PM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-12-11 3:19 PM, Larry Martell wrote: I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. Explicit join syntax makes such queries easier to read, write, develop debug. Is this what you mean? SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Thanks very much for the reply. This is giving me: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? TIA! -larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
2012/12/11 16:19 -0500, Larry Martell I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? Modern forms do not give a left join if one uses WHERE-clause to reduce a full cross-join to an inner join. It is better to start with something like this, FROM data_cst JOIN data_target ON data_target.id = data_cst.target_name_id JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id JOIN (data_category JOIN data_tool ON data_category.id = data_tool.category_id) ON data_tool.id = data_cst.tool_id but I am not too sure where to bracket data_tool. When you have put it into a 'FROM'-clause with 'JOIN', not comma, separating the tables, with the same outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'. Maybe you want it between data_cstimage and data_measparams. (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. Then a bit of reordering is required ... SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; PB On 2012-12-11 5:11 PM, Larry Martell wrote: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Thanks very much for the reply. This is giving me: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool.
Re: JOIN, JOIN, JOIN
2012/08/22 17:38 -0400, Shawn Green MySQL does not have a simple OUTER JOIN command (some RDBMSes call this a FULL OUTER JOIN). What we do have is the option to include the OUTER keyword into our LEFT or RIGHT joins. For example, both of these are acceptable: LEFT OUTER JOIN LEFT JOIN Also, you need a space between inner and join as in INNER JOIN. If you want only the most restricitive criteria that match resultsets from both select statements use INNER JOIN if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN Again, MySQL does not have a plain OUTER JOIN. If you want a full Cartesian product of two tables, use the 'comma join' syntax with no criteria for matching the tables in the WHERE clause. SELECT ... FROM tableA, tableB WHERE Syntax details are located here: http://dev.mysql.com/doc/refman/5.5/en/join.html which last is the same as SELECT ... FROM tableA JOIN tableB WHERE ; that is, if there is no join_condition that, too, becomes a cross-join. From the webpage: In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. That is, in MySQL neither INNER nor CROSS has any meaning. If there is no restriction (ON/USING/NATURAL) it is CROSS JOIN; otherwise, something else. As for OUTER, in MySQL it is a word that may appear between LEFT/RIGHT and JOIN; it is not really meaningful; as Shawn Green said, MySQL has no full outer join (but Oracle has!). Therefore, in case of syntactical parsimony, whereto, it seems to me, MySQL is headed, one writes JOIN by itself with restrictions or not (for cross join), or after one of the words LEFT, RIGHT(, OUTER when it is implemented) with restrictions. In case of syntactical security (the standard?), one always qualifys JOIN: with CROSS if one intends no restriction, else with INNER or OUTER, and qualifys the latter with LEFT or RIGHT if one intends other than full outer join. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: JOIN giving duplicate records
; 2012/04/03 18:18 +0100, Tompkins Neil Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. It is in JOIN s nature to multiply output, but unless you show your whole query, noöne can comment on it. As for DISTINCT, its purpose is as you saw, to eliminate duplicates. Sometimes it is the best means for it--but you show too little for comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
JOIN giving duplicate records
Hi Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. Thanks, Neil
Re: strange select/join/group by with rollup issue....
I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur
Re: strange select/join/group by with rollup issue....
Thanks, it seems to be working now. I just discovered WITH ROLLUP. It made me very happy on this project... On 2/8/12 2:54 AM, Arthur Fuller wrote: I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
strange select/join/group by with rollup issue....
I am having a problem with select results that I don't understand. It seems to be tied up with a GROUP BY statement. Forgive the complexity of the SQL, I inherited some problematic data structuring. If I use this statement: SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-07' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP I get what I expect, having a number of rows where the client.created date is 2012-02-07. But if I change it to this (the only change is the from date): SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_IDX_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-01' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP The results contain no data with client.created = 2012-02-07. If I get rid of the group by (and the count(*)), there are rows with all 7 dates. I have tried changing the to date from '2012-02-07' to '2012-02-08', in case this was a less than issue, but that doesn't change. Why is the group by dropping the last date of my data? thanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: What is wrong with this outer join?
On 10/19/2011 20:03, Dotan Cohen wrote:
...
Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?
MySQL has always encouraged its employees (developers, support,
documentation, marketing, ... anyone) to listen to and keep up with the
community feedback channels. This philosophy has existed since the
beginning of the lists and forums.
If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.
We do! First though, are you referencing the online documentation or the
packaged documentation? The reason I ask is that the online
documentation does have some user contributions and comments to go along
with the text itself. That outside content is not included with the
packaged documentation.
I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.
No offense taken. We are always trying to keep MySQL easy to install,
easy to operate, and easy to learn. All feedback is valid.
Regards,
--
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?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On 10/20/2011 9:11 AM, Shawn Green (MySQL) wrote:
On 10/19/2011 20:03, Dotan Cohen wrote:
...
Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?
MySQL has always encouraged its employees (developers, support,
documentation, marketing, ... anyone) to listen to and keep up with
the community feedback channels. This philosophy has existed since the
beginning of the lists and forums.
Shawn,
Yes indeed, and your determination to maintain the philosophy that's
characterised the MySQL project from its beginning is heartening.
Here's some feedback that's in the bailiwick of the MySQLWebmaster, but
sending it there brings no response and has no effect. The Newbie,
General and a few other MySQL fora have been overrun with spam for
weeks, to the point of unusability, without effective remediation.
What's going on?
PB
-
If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.
We do! First though, are you referencing the online documentation or
the packaged documentation? The reason I ask is that the online
documentation does have some user contributions and comments to go
along with the text itself. That outside content is not included with
the packaged documentation.
I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.
No offense taken. We are always trying to keep MySQL easy to install,
easy to operate, and easy to learn. All feedback is valid.
Regards,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Thu, Oct 20, 2011 at 16:11, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: We do! First though, are you referencing the online documentation or the packaged documentation? The reason I ask is that the online documentation does have some user contributions and comments to go along with the text itself. That outside content is not included with the packaged documentation. I am in fact referring to the online documentation. Official documentation examples would help. Take for instance this page from the PHP manual for instance: http://il2.php.net/manual/en/function.mail.php Four usage examples including code covering real-world scenarios. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What is wrong with this outer join?
mysql select * from beers; ++---++ | ID | name | colour | ++---++ | 1 | carlsburg | 2 | | 2 | tuburg| 1 | | 3 | tuburg| 9 | ++---++ 3 rows in set (0.00 sec) mysql select * from colours; +++ | id | colour | +++ | 1 | red| | 2 | green | | 3 | blue | +++ 3 rows in set (0.00 sec) mysql select * from beers inner join colours on beers.colour = colours.ID; ++---++++ | ID | name | colour | id | colour | ++---++++ | 1 | carlsburg | 2 | 2 | green | | 2 | tuburg| 1 | 1 | red| ++---++++ 2 rows in set (0.00 sec) mysql select * from beers outer join colours on beers.colour = colours.ID; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours on beers.colour = colours.ID' at line 1 So I've gone looking the fine manual, here: http://dev.mysql.com/doc/refman/5.6/en/join.html The manual references natural outer joins and requires curly brackets and I'm frankly not making sense of it. Left, right, and inner joins work as I expect them too, and fishing for examples in google doesn't find anything unusual. How exactly am I erring? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 16:33, Michael Dykman mdyk...@gmail.com wrote: Try this. I sometime get wierd results when I fail to use aliases in a join. Also, the parentheses are required. - md select * from beers b inner join colours c on (b.colour = c.ID); Thank you Michael. That does work, however when I convert it to an outer join I get the same error as before: mysql select * from beers b inner join colours c on (b.colour = c.ID); ++---++++ | ID | name | colour | id | colour | ++---++++ | 1 | carlsburg | 2 | 2 | green | | 2 | tuburg| 1 | 1 | red| ++---++++ 2 rows in set (0.30 sec) mysql select * from beers b outer join colours c on (b.colour = c.ID); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours c on (b.colour = c.ID)' at line 1 mysql -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
Hello Dotan,
On 10/19/2011 09:57, Dotan Cohen wrote:
mysql select * from beers;
++---++
| ID | name | colour |
++---++
| 1 | carlsburg | 2 |
| 2 | tuburg| 1 |
| 3 | tuburg| 9 |
++---++
3 rows in set (0.00 sec)
mysql select * from colours;
+++
| id | colour |
+++
| 1 | red|
| 2 | green |
| 3 | blue |
+++
3 rows in set (0.00 sec)
mysql select * from beers inner join colours on beers.colour = colours.ID;
++---++++
| ID | name | colour | id | colour |
++---++++
| 1 | carlsburg | 2 | 2 | green |
| 2 | tuburg| 1 | 1 | red|
++---++++
2 rows in set (0.00 sec)
mysql select * from beers outer join colours on beers.colour = colours.ID;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'outer join colours on beers.colour = colours.ID'
at line 1
So I've gone looking the fine manual, here:
http://dev.mysql.com/doc/refman/5.6/en/join.html
The manual references natural outer joins and requires curly brackets
and I'm frankly not making sense of it. Left, right, and inner joins
work as I expect them too, and fishing for examples in google doesn't
find anything unusual. How exactly am I erring?
Thanks!
This is a simple misunderstanding. From the page you quote, the syntax
patterns for an OUTER join are these:
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference
join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
Notice that in the second, the [OUTER] is nested inside of [{LEFT|RIGHT}
[OUTER]] and in the first it follows the NON-OPTIONAL choice of
{LEFT|RIGHT). Neither one of these syntax patterns allows the keyword
OUTER to appear without either the LEFT or RIGHT keyword before it.
To make this crystal clear those patterns allow LEFT JOIN, RIGHT JOIN,
LEFT OUTER JOIN, or RIGHT OUTER JOIN but not just OUTER JOIN.
Regards,
--
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?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 18:00, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
This is a simple misunderstanding. From the page you quote, the syntax
patterns for an OUTER join are these:
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
Notice that in the second, the [OUTER] is nested inside of [{LEFT|RIGHT}
[OUTER]] and in the first it follows the NON-OPTIONAL choice of
{LEFT|RIGHT). Neither one of these syntax patterns allows the keyword OUTER
to appear without either the LEFT or RIGHT keyword before it.
To make this crystal clear those patterns allow LEFT JOIN, RIGHT JOIN, LEFT
OUTER JOIN, or RIGHT OUTER JOIN but not just OUTER JOIN.
Thank you Shawn! I see that I am getting support right from the top!
So far as I understand, an outer join should return all matched and
unmatched rows (essentially all rows) from both tables. So it is not
clear to me what is the difference between a right outer join and a
left outer join, and how they differ from a regular outer join. But
don't answer that, I'll google it and post back for the fine archives.
Thanks!
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On 10/19/2011 13:19, Dotan Cohen wrote: ... Thank you Shawn! I see that I am getting support right from the top! So far as I understand, an outer join should return all matched and unmatched rows (essentially all rows) from both tables. So it is not clear to me what is the difference between a right outer join and a left outer join, and how they differ from a regular outer join. But don't answer that, I'll google it and post back for the fine archives. What you are describing is a FULL OUTER JOIN. This is not supported, yet, in MySQL. We only support INNER, NATURAL, LEFT, and RIGHT. To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a RIGHT like this: ( SELECT ... FROM basetable LEFT JOIN jointable ON basetable.PKID = jointable.base_id ) UNION ALL( SELECT ... FROM basetable RIGHT JOIN JOINtable ON basetable.PKID = jointable.base_id ... WHERE basetable.PKID is NULL ... ) The first half of the UNION finds all rows in basetable plus any rows where the jointable matches. The second half identifies only rows in jointable that have no match with a row in basetable. -- 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?unsub=arch...@jab.org
Re: What is wrong with this outer join?
On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
What you are describing is a FULL OUTER JOIN. This is not supported, yet, in
MySQL. We only support INNER, NATURAL, LEFT, and RIGHT.
To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a
RIGHT like this:
(
SELECT ...
FROM basetable
LEFT JOIN jointable
ON basetable.PKID = jointable.base_id
) UNION ALL(
SELECT ...
FROM basetable
RIGHT JOIN JOINtable
ON basetable.PKID = jointable.base_id
...
WHERE basetable.PKID is NULL
...
)
The first half of the UNION finds all rows in basetable plus any rows where
the jointable matches. The second half identifies only rows in jointable
that have no match with a row in basetable.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thank you Shawn. I very much appreciate your help, and I also
appreciate your employer's initiative to have such a position
monitoring the mailing list. Is that an Oracle-created position, or
did it exist at Sun as well?
If I'm already talking with the MySQL Principal Technical Support
Engineer then I have to suggest that the MySQL manual include more
example code. I'm a read-the-manual kind of guy and the C# / PHP
manuals are usually enough to get me unstuck. The MySQL and Java (only
mentioned as it is another Sun/Oracle product) manuals usually do not
provide code examples and I must google for them from unreliable blogs
and forum postings. I personally find concise code examples much more
intuitive and informative than full-format [{(someOption |
anotherOption), somethingHere} rarelyUsedFeature] which I might or
might not mentally parse. I can gladly make more specific suggestions
if Oracle sees the idea as actionable.
I mention this as constructive criticism, take no offense! I'm only at
the beginning of my career and I don't claim to have the expertise or
experience to tell Oracle how to run their show, I only voice my
concern as a consumer of the product and one with an interest in
keeping the product and technology viable. I have nothing but
appreciation to Oracle for continuing to develop Java, MySQL and for
having the good sense to pass OOo onto the Apache foundation.
Thank you.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is wrong with this outer join?
; 2011/10/19 17:00 +0200, Dotan Cohen mysql select * from beers b outer join colours c on (b.colour = c.ID); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join colours c on (b.colour = c.ID)' at line 1 MySQL does not have real outer join, only left right join. One means of getting outer join is the union of left join and right join. (The words INNER and OUTER have no real meaning in MySQL.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join 2 tables and compare then calculate something
Try out query with UNION also select A,B,C from (select A,B,C from X UNION select A,B,C from Y) group by A,B,C having count(*)1 From: Gian Karlo C webdev...@gmail.com To: mysql@lists.mysql.com Sent: Sunday, 2 October 2011 12:49 PM Subject: Join 2 tables and compare then calculate something Hi All, I decided to join and write to the list hoping someone could help and shed a light on me. Here's the scenario. I have a database running in mysql 5.x in Centos 5. The database has 2 tables that is almost identical with some additional fields. Table 1 Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived Table 2 Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived, Owner Here's my SQL statement to compare both tables if fields data are the same then consider it as a valid record. select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue, Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND Table1.IPAddress = Table2.IPAddress AND Table1.Description = Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity = Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1; I need to compare Name, IPAddress, Description, Issue, Severity and Timestamp to consider as I valid data then I group it so that only one record will show although there is no duplicate entry on the results. Just want to make sure. Using that SQL statement I was able to get and compare data (which I don't know if this is a good approach), now when I get a valid results, I want to compute the DateReceived. Table1 DateReceived = 10:05 Table2 DateReceived = 10:15 So the computation is to get the difference between DateReceived which the result should be 10 minutes. How would I add that computation to my existing SQL statement and maybe someone suggest a good approach with regards to my current statement. Thanks in advance.
In general, cheaper to INNER JOIN or two separate queries
I need two fields from two different tables. I could either run two queries, or a single INNER JOIN query: $r1=mysql_query(SELECT fruit FROM fruits WHERE userid = 1); $r2=mysql_query(SELECT beer FROM beers WHERE userid = 1); --or-- $r=mysql_query(SELECT fruits.fruit, beers.beer FROM fruits INNER JOIN beers ON fruits.userid = beers.userid WHERE beers.userid = 1); In general, which is preferable? I don't have access to the production machine to benchmark at the moment, but which is best practice? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join 2 tables and compare then calculate something
Hi All, I decided to join and write to the list hoping someone could help and shed a light on me. Here's the scenario. I have a database running in mysql 5.x in Centos 5. The database has 2 tables that is almost identical with some additional fields. Table 1 Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived Table 2 Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived, Owner Here's my SQL statement to compare both tables if fields data are the same then consider it as a valid record. select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue, Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND Table1.IPAddress = Table2.IPAddress AND Table1.Description = Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity = Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1; I need to compare Name, IPAddress, Description, Issue, Severity and Timestamp to consider as I valid data then I group it so that only one record will show although there is no duplicate entry on the results. Just want to make sure. Using that SQL statement I was able to get and compare data (which I don't know if this is a good approach), now when I get a valid results, I want to compute the DateReceived. Table1 DateReceived = 10:05 Table2 DateReceived = 10:15 So the computation is to get the difference between DateReceived which the result should be 10 minutes. How would I add that computation to my existing SQL statement and maybe someone suggest a good approach with regards to my current statement. Thanks in advance.
Re: Update on inner join - looks good to me, where did I go wrong?
On Sat, Sep 10, 2011 at 01:48, Carsten Pedersen cars...@bitbybit.dk wrote: `userTable.userid` = `userTable`.`userid` Thank you Carsten. That was indeed the problem! Have a peaceful weekend. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update on inner join - looks good to me, where did I go wrong?
I'm trying to update on an join, but I can't find my error: UPDATE `userTable` SET `someField`=Jimmy Page FROM `userTable` INNER JOIN `anotherTable` ON `userTable.userid`=`anotherTable.userid` WHERE `userTable.someField`=Jim Morrison AND `anotherTable.date` NOW(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM That error is for reserved words, and I am escaping all the fields and tables (using the backticks). So why the error? This is on a CentOS 4 or 5 server, with MySQL 5.0.77, accessed from the CLI. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` - ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison - AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
`userTable.userid` = `userTable`.`userid` / Carsten On 09-09-2011 23:01, Dotan Cohen wrote: Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` -ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison -AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Nuno Tavares [mailto:nuno.tava...@dri.pt] Sent: Tuesday, May 03, 2011 6:21 PM To: mysql@lists.mysql.com Subject: Re: Join based upon LIKE Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. [JS] Definitely -- but I have to work with the tools available. This is only one part of the process, there is more trouble further on that is not related to our database at all. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. [JS] I never heard of Google Refine. Thanks for bringing to my attention. Hope it helps, -NT [JS] Thank you. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should
Re: Join based upon LIKE
http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, May 03, 2011 5:31 AM To: Jerry Schwartz Cc: Jim McNeely; mysql mailing list; Johan De Meersman Subject: Re: Join based upon LIKE http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. [JS] Thanks. I'm not sure that I could easily build a dictionary of non-junk words, since some of these reports have titles like Toluene Diisocyanate Market Outlook 2008, Toluene Market Outlook 2008, and Toluene: 2009 World Market Outlook And Forecast (Special Crisis Edition). I shall ponder this when I am caught up, or (more likely) in the afterlife. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com
RE: Join based upon LIKE
My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. Hope it helps, -NT Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Sunday, May 01, 2011 4:01 AM To: Jerry Schwartz Cc: Jim McNeely; mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE `wild_title`. Roughly what I meant with the shadow fields, yes - keep your own set of data around :-) I have little more to offer, then, I'm afraid. The soundex() algorithm may or may not be of some use to you; it offers comparison based (roughly) on pronounciation instead of spelling. [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. Apart from that, you have my deepest sympathy. I hope you can wake up from the nightmare soon :-) [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE `wild_title`. Roughly what I meant with the shadow fields, yes - keep your own set of data around :-) I have little more to offer, then, I'm afraid. The soundex() algorithm may or may not be of some use to you; it offers comparison based (roughly) on pronounciation instead of spelling. Apart from that, you have my deepest sympathy. I hope you can wake up from the nightmare soon :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: Join based upon LIKE
2011/04/28 15:28 -0400, Jerry Schwartz No takers? And this is not real taking, because the algorithm of which I am thinking, the edit-distance (Levens(h)tein-distance) algorithm costs too much for you (see the Wikipedia entry). The obvious implementation takes as many steps as the product of the two compared strings s length. On the other hand, a good implementation of LIKE costs the pattern s length added to all the strings against which it matches s length, a sum, not product, of lengths. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: Join based upon LIKE
2011/04/28 15:28 -0400, Jerry Schwartz No takers? And this is not real taking, because the algorithm of which I am thinking, the edit-distance (Levens(h)tein-distance) algorithm costs too much for you (see the Wikipedia entry), but it yields, I believe, much more nearly such answer as you want. The obvious implementation takes as many steps as the product of the two compared strings s length. On the other hand, a good implementation of LIKE costs the pattern s length added to all the strings against which it matches s length, a sum, not product, of lengths. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. Heh. What you *really* need, is a LART. Preferably one of the spiked variety. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Yes, unfortunately. You should see if you can introduce a form of data normalisation - say, shadow fields with corrected entries, or functionality in the application that suggests correct entries based on what the user typed. Or, if the money's there, you could have a look at Amazon Mechanical Turk (yes, really) for cheap-ish data correction. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Friday, April 29, 2011 5:56 AM To: Jerry Schwartz Cc: mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. Heh. What you *really* need, is a LART. Preferably one of the spiked variety. [JS] Unless a LART is a demon of some kind, I don't know what it is. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Yes, unfortunately. You should see if you can introduce a form of data normalisation - say, shadow fields with corrected entries, or functionality in the application that suggests correct entries based on what the user typed. [JS] Except for obvious misspellings and non-ASCII characters, I do not have the freedom to muck with the text. If the data were created in-house, I could correct it on the way in; but it comes from myriad other companies. Or, if the money's there, you could have a look at Amazon Mechanical Turk (yes, really) for cheap-ish data correction. [JS] Again, I can't change the data. The titles are assigned by the publishers. Think what would happen if Amazon decided to fix the titles of books. Ain't Misbehavin would, at best, turn into I am not misbehaving. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message-
From: Jim McNeely [mailto:j...@newcenturydata.com]
Sent: Thursday, April 28, 2011 6:43 PM
To: Jerry Schwartz
Subject: Re: Join based upon LIKE
It just smells wrong, a nicer system would have you joining on ID's of some
kind so that spelling wouldn't matter. I don't know the full situation for
you
though.
[JS] That would be nice, wouldn't it.
In a nutshell, we sell publications. Publishers send us lists of publications.
Some are new, some replace previous editions. (Think of books, almanacs, and
newsletters.) Some publishers make do without any product IDs at all, but most
do use product IDs of some kind.
The problem is that the March edition of a publication might or might not have
the same product ID as the February edition. I try to match them both by
product ID and by title. Sometimes the title will fuzzy match, but the ID
won't; sometimes the ID will match but the title won't; sometimes (if I'm
really lucky) they both match; and sometimes the ID matches one product and
the title matches another.
It's the fuzzy match by title that gives me fits:
- The title might have a date in it (Rain in Spain in 2010 Q2), but not
necessarily in a uniform way (Rain in Spain Q3 2010).
- The title might have differences in wording or punctuation (Rain in Spain -
2010Q2).
- The title might have simple misspellings (Rain in Spian - Q2 2010).
I've written code that looks for troublesome constructs and replaces them with
%: in , -, to , Q2, 2Q, and more and more. So Rain in Spain -
2010 Q2 becomes Rain%Spain%.
I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE
`wild_title`.
This will miss actual misspellings (Spain, Spian). It will also produce a
large number of false positives.
On the back end, I have other code that compares the new titles against the
titles retrieved by that query and decides if they are exact matches,
approximate matches (here I do use regular expressions, as well as lists of
known bad boys), or false positives. From there on, it's all hand work.
Pretty big nut, eh?
So that's why I need to use LIKE in my JOIN.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
Jim McNeely
On Apr 28, 2011, at 12:28 PM, Jerry Schwartz wrote:
No takers?
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Monday, April 25, 2011 2:34 PM
To: 'Mailing-List mysql'
Subject: Join based upon LIKE
I have to match lists of new publications against our database, so that I
can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their
titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends,
April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details
of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas
Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE
(new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows
left join two tables
Hi!! Is it possible to create a left join consult with 2 tables?? I mean: SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID = table2.subID and table1.ID= table3.subID Thanks!!! Rocío Gómez Escribano mailto:r.sanc...@ingenia-soluciones.com r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com
Re: left join two tables
Hey there, - Original Message - From: Rocio Gomez Escribano r.go...@ingenia-soluciones.com Hi!! Is it possible to create a left join consult with 2 tables?? I mean: SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID = table2.subID and table1.ID= table3.subID Pretty close already. Might I suggest sampling the fine manual ? Have a look at http://dev.mysql.com/doc/refman/5.0/en/join.html -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: left join two tables
Hi Johan, I think you probably want something like this. Give the following a shot. SELECT * FROM table1 LEFT JOIN table2 ON table1.ID = table2.subID LEFT JOIN table3 ON table1.ID= table3.subID On Thu, Apr 28, 2011 at 9:41 AM, Johan De Meersman vegiv...@tuxera.be wrote: Hey there, - Original Message - From: Rocio Gomez Escribano r.go...@ingenia-soluciones.com Hi!! Is it possible to create a left join consult with 2 tables?? I mean: SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID = table2.subID and table1.ID= table3.subID Pretty close already. Might I suggest sampling the fine manual ? Have a look at http://dev.mysql.com/doc/refman/5.0/en/join.html -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
FW: Join based upon LIKE
No takers?
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Monday, April 25, 2011 2:34 PM
To: 'Mailing-List mysql'
Subject: Join based upon LIKE
I have to match lists of new publications against our database, so that I can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends, April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE (new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47
Extra: Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id
key: pub_id
key_len: 48
ref: const
rows: 19607
Extra: Using where
=
Here's the important part of the table `prod`:
=
Table: prod
Create Table: CREATE TABLE `prod` (
`prod_id` varchar(15) NOT NULL DEFAULT '',
`prod_num` mediumint(6) unsigned DEFAULT NULL,
`prod_title` varchar(255) DEFAULT NULL,
`prod_type` varchar(2) DEFAULT NULL,
`prod_vat_pct` decimal(5,2) DEFAULT NULL,
`prod_discont` tinyint(1) DEFAULT NULL,
`prod_replacing` mediumint(6) unsigned DEFAULT NULL,
`prod_replaced_by` mediumint(6) unsigned DEFAULT NULL,
`prod_ready` tinyint(1) DEFAULT NULL,
`pub_id` varchar(15) DEFAULT NULL,
...
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_num` (`prod_num`),
KEY `prod_pub_prod_id` (`prod_pub_prod_id`),
KEY `pub_id` (`pub_id`),
KEY `prod_title` (`prod_title`),
FULLTEXT KEY `prod_title_fulltext` (`prod_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
=
This works reasonably well for a small number (perhaps 200-300) of new
products; but now I've been handed a list of over 15000 to stuff into the
table `new_titles`! This motivates me to wonder if there is a better way,
since I expect this to take a very long time.
Suggestions?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp No takers? Not willingly, no :-p This is a pretty complex problem, as SQL itself isn't particularly well-equipped to deal with fuzzy data. One approach that might work is using a fulltext indexing engine (MySQL's built-in ft indices, or an external one like Solr or something) and doing best-fit matches on the keywords of the title you're looking for. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, April 28, 2011 4:18 PM To: Jerry Schwartz Cc: mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp No takers? Not willingly, no :-p This is a pretty complex problem, as SQL itself isn't particularly well- equipped to deal with fuzzy data. One approach that might work is using a fulltext indexing engine (MySQL's built-in ft indices, or an external one like Solr or something) and doing best-fit matches on the keywords of the title you're looking for. [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
I would go with join rather than where condition. 2011/4/26 Halász Sándor h...@tbbs.net 2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
Hello Halбsz, Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile My blog: http://oire.org/menelion (mostly in Russian) Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion Original message From: Halбsz Sбndor h...@tbbs.net To: Andre Polykanine Date created: , 7:00:03 AM Subject: LEFT JOIN and WHERE: identical or not and what is better?, etc. 2011/04/25 17:42 +0300, Andre Polykanine Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? 2011/04/25 10:16 -0500, Johnny Withers The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. ... I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id` That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
2011/04/26 17:55 +0300, Andre Polykanine Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! I think so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LEFT JOIN and WHERE: identical or not and what is better?
Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
'where' is a filter. You're limiting records based on a criterion. 'on' is used for joining. On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine an...@oire.org wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mmal...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
I can be wrong about that, but I think the difference between them should be irrelevant so it makes me think about a paranoiac thought. For me, the only difference is: Chose the one you feel better to understand your code. Am I wrong or not? -- João Cândido de Souza Neto Andre Polykanine an...@oire.org escreveu na mensagem news:199779304.20110425174...@oire.org... Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
The only difference once MySQL parses these two queries is the first one is a LEFT JOIN, which will produce all records from the blogs table even if there is no matching record in the users table. The second query produces an INNER JOIN which means only rows with matching records in both tables will be returned. Which one is faster? Probably the second since NULLs do not have to be considered -- probably not much faster though. Which one is better? That'll depend on your needs, if you only need records from both tables that have a matching row in the other, the second is better. If you need all blogs, even those without a matching user (can that even occur?), the first one is better. I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. JW On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine an...@oire.org wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LEFT JOIN and WHERE: identical or not and what is better?
Hi Andre, everybody! Andre Polykanine wrote: Hello everyone, Sorry for my beginner question. Actually I have been using MySql for a long time but I just start using some advanced things (earlier I accomplished those tasks with PHP), so I will be asking stupid questions, please bear with me. Here is the first one. We have two queries: SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId`=`Users`.`Id`; and the following one: SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE `Blogs`.`UserId`=`Users`.`Id`; 1. Are they identical? 2. Which is better (faster, more optimal, more kosher, I mean, better style...)? In your subject line, you are mixing unrelated things: - LEFT JOIN is an alternative to INNER JOIN. It tell the database to return not only matching row combinations but also those where the first (left-hand) table has a row with a NULL column. - ON is an alternative to WHERE. For both aspects, the manual has more information than I will be able to think of in this mail. So the real difference between your statements is not LEFT JOIN vs WHERE, or ON vs WHERE, it is LEFT JOIN vs inner join. It will become important if you have rows in table Blogs whose column UserId holds NULL rather than any definite value. To understand that, you will have to read about NULL and the three-valued logic of SQL (whose comparisons can return true, false, and unknown). In general, an inner join should be faster - but who cares? SQL statements must be coded for correctness first, not for speed - and that will determine your choice of LEFT JOIN vs inner join. Tuning and optimization come later (in priority). The same holds for style etc: Clean programming is nice (and helpful in understanding and maintenance), but correctness comes first. Regards, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join based upon LIKE
I have to match lists of new publications against our database, so that I can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends, April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE (new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47
Extra: Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id
key: pub_id
key_len: 48
ref: const
rows: 19607
Extra: Using where
=
Here's the important part of the table `prod`:
=
Table: prod
Create Table: CREATE TABLE `prod` (
`prod_id` varchar(15) NOT NULL DEFAULT '',
`prod_num` mediumint(6) unsigned DEFAULT NULL,
`prod_title` varchar(255) DEFAULT NULL,
`prod_type` varchar(2) DEFAULT NULL,
`prod_vat_pct` decimal(5,2) DEFAULT NULL,
`prod_discont` tinyint(1) DEFAULT NULL,
`prod_replacing` mediumint(6) unsigned DEFAULT NULL,
`prod_replaced_by` mediumint(6) unsigned DEFAULT NULL,
`prod_ready` tinyint(1) DEFAULT NULL,
`pub_id` varchar(15) DEFAULT NULL,
...
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_num` (`prod_num`),
KEY `prod_pub_prod_id` (`prod_pub_prod_id`),
KEY `pub_id` (`pub_id`),
KEY `prod_title` (`prod_title`),
FULLTEXT KEY `prod_title_fulltext` (`prod_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
=
This works reasonably well for a small number (perhaps 200-300) of new
products; but now I've been handed a list of over 15000 to stuff into the
table `new_titles`! This motivates me to wonder if there is a better way,
since I expect this to take a very long time.
Suggestions?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Mon, Jan 24, 2011 at 6:43 PM, Gavin Towey gto...@ffn.com wrote: If you show the EXPLAIN SELECT .. output, and the table structure, someone will be able to give a more definite answer. Thanks for the reply Gavin. I actually did place this info in my very first message on this thread, along with my basic table structure and server version. Myself and others have just stopped keeping the full, deeply-nested, quoted thread inside all subsequent messages which is why you probably haven't seen it. However, here is the EXPLAIN SELECT from the first message (reformatted for email): select_type: SIMPLE table: recipients type: ref possible_keys: messages_fk, employee_idx key: employee_idx key_len: 5 ref: const rows: 222640 Extra: Using where; Using temporary; Using filesort select_type: SIMPLE table: messages type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: email_archive.recipients.message_id rows: 1 Extra: Anyhow, having now copied these tables to another server (MySQL 5.1) and done some tests (bumping up innodb_buffer_pool_size and playing with innodb_flush_log_at_trx_commit for my writes and a few other knobs) it is simply that these somewhat large tables need lots of RAM to perform well, just as Reindl Harald originally pointed out. Thanks again for the help everyone! -- Kendall Gifford zettab...@gmail.com
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
Hi everybody! Shawn Green (MySQL) wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) [[ ... see the original post for the schema details ... ]] I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. [[...]] You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. [[...]] I don't want to contradict Shawn, but currently I fail to see the need for the GROUP BY: Joining like this messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X can return only one row, unless there are multiple recipients records for the same values of message_id and employee_id. I don't know whether that can happen in the poster's application, and whether it would cause trouble if the result line would occur multiple times. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe joerg.bru...@oracle.comwrote: Hi everybody! Shawn Green (MySQL) wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) [[ ... see the original post for the schema details ... ]] I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. [[...]] You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. [[...]] I don't want to contradict Shawn, but currently I fail to see the need for the GROUP BY: Joining like this messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X can return only one row, unless there are multiple recipients records for the same values of message_id and employee_id. I don't know whether that can happen in the poster's application, and whether it would cause trouble if the result line would occur multiple times. In my application, there CAN in fact be several recipients records with both the same message_id foreign key value AND the same employee_id value (some employees may be a recipient of a message several times over via alternative addresses and/or aliases). However, as I rework things, I could probably rework application logic nuke the GROUP BY and just cope, in code, with these extra messages records in my result set. (Just FYI, the SQL query is simply the default query as created by rails or, more specifically, ActiveRecord 2.3.9 which I can/will-be optimizing). I will additionally be moving this database to a new server. However, for academic interest, I'll see if I can make time to post the query time(s) once I change the app, before moving the database to a new (and better configured) server. Thanks for the help everybody. Regards, Jörg -- Kendall Gifford zettab...@gmail.com
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Mon, Jan 24, 2011 at 2:20 PM, Kendall Gifford zettab...@gmail.comwrote: On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe joerg.bru...@oracle.comwrote: Hi everybody! Shawn Green (MySQL) wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) [[ ... see the original post for the schema details ... ]] I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. [[...]] You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. [[...]] I don't want to contradict Shawn, but currently I fail to see the need for the GROUP BY: Joining like this messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X can return only one row, unless there are multiple recipients records for the same values of message_id and employee_id. I don't know whether that can happen in the poster's application, and whether it would cause trouble if the result line would occur multiple times. In my application, there CAN in fact be several recipients records with both the same message_id foreign key value AND the same employee_id value (some employees may be a recipient of a message several times over via alternative addresses and/or aliases). However, as I rework things, I could probably rework application logic nuke the GROUP BY and just cope, in code, with these extra messages records in my result set. (Just FYI, the SQL query is simply the default query as created by rails or, more specifically, ActiveRecord 2.3.9 which I can/will-be optimizing). I will additionally be moving this database to a new server. However, for academic interest, I'll see if I can make time to post the query time(s) once I change the app, before moving the database to a new (and better configured) server. Just an update for posterity, simply removing the GROUP BY clause of my query above has, overall, no noticeable effect on performance. I suspect server configuration, as pointed out by Reindl, is too much of a bottleneck and is what I first need to change (working on that now). Perhaps the removal of GROUP BY would/will be noticeable if the server configuration for InnoDB tables wasn't so horrendous. I'll find out... -- Kendall Gifford zettab...@gmail.com
RE: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
If you show the EXPLAIN SELECT .. output, and the table structure, someone will be able to give a more definite answer. -Original Message- From: Kendall Gifford [mailto:zettab...@gmail.com] Sent: Monday, January 24, 2011 2:29 PM To: mysql@lists.mysql.com Subject: Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables On Mon, Jan 24, 2011 at 2:20 PM, Kendall Gifford zettab...@gmail.comwrote: On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe joerg.bru...@oracle.comwrote: Hi everybody! Shawn Green (MySQL) wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) [[ ... see the original post for the schema details ... ]] I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. [[...]] You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. [[...]] I don't want to contradict Shawn, but currently I fail to see the need for the GROUP BY: Joining like this messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X can return only one row, unless there are multiple recipients records for the same values of message_id and employee_id. I don't know whether that can happen in the poster's application, and whether it would cause trouble if the result line would occur multiple times. In my application, there CAN in fact be several recipients records with both the same message_id foreign key value AND the same employee_id value (some employees may be a recipient of a message several times over via alternative addresses and/or aliases). However, as I rework things, I could probably rework application logic nuke the GROUP BY and just cope, in code, with these extra messages records in my result set. (Just FYI, the SQL query is simply the default query as created by rails or, more specifically, ActiveRecord 2.3.9 which I can/will-be optimizing). I will additionally be moving this database to a new server. However, for academic interest, I'll see if I can make time to post the query time(s) once I change the app, before moving the database to a new (and better configured) server. Just an update for posterity, simply removing the GROUP BY clause of my query above has, overall, no noticeable effect on performance. I suspect server configuration, as pointed out by Reindl, is too much of a bottleneck and is what I first need to change (working on that now). Perhaps the removal of GROUP BY would/will be noticeable if the server configuration for InnoDB tables wasn't so horrendous. I'll find out... -- Kendall Gifford zettab...@gmail.com IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Slow query on MySQL4 server doing simple inner join of two InnoDB tables
Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). -- Kendall Gifford zettab...@gmail.com
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
you need hughe ram / innodb_buffer_pool for large datasets in a perfect world the buffer_pool is as large as the data how looks your current config? how much RAM has the machine? Am 21.01.2011 20:21, schrieb Kendall Gifford: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. Something like this CREATE TEMPORARY TABLE tmpMessages (message_id INT UNSIGNED, PRIMARY KEY (message_id)) ENGINE=MEMORY; INSERT IGNORE tmpMessages SELECT message_id FROM recipients WHERE employee_id = X; SELECT messages.* FROM messages INNER JOIN tmpMessages ON tmpMessages.message_id = messages.id ORDER BY sent_at DESC LIMIT 0, 25; By pre-selecting a limited set of message_id values from the recipients table, you seriously reduce the number of rows that need to be scanned. Also, the INSERT IGNORE technique is faster than the GROUP BY because it uses an index to identify any duplicates instead of a scan of all previous unique values. Please let us all know if this is faster enough. (and don't forget to drop the temp table once you are through using it) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
On Fri, Jan 21, 2011 at 2:01 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 1/21/2011 14:21, Kendall Gifford wrote: Hello everyone, I've got a database on an old Fedora Core 4 server running MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has just two (InnoDB) tables: messages (approx 2.5 million records) recipients (approx 6.5 million records) These track information about email messages. Each message has many recipient records. The structure of the two tables (omitting irrelevant data fields) are as follows: +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | sent_at | datetime | | MUL | -00-00 00:00:00 || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | message_id | int(10) unsigned | | MUL | 0 || | employee_id | int(10) unsigned | YES | MUL | NULL || | . OTHER FIELDS OMITTED FOR BREVITY ... | +-+--+--+-+-++ I have the following query that is just too slow: SELECT messages.* FROM messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X GROUP BY messages.id ORDER BY sent_at DESC LIMIT 0, 25; This takes about 44 seconds on average. The query explanation is as follows: ++-+++--+--+-+-++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+++--+--+-+-++--+ | 1 | SIMPLE | recipients | ref| messages_fk,employee_idx | employee_idx | 5 | const | 222640 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | messages | eq_ref | PRIMARY | PRIMARY | 4 | email_archive.recipients.message_id | 1 | | ++-+++--+--+-+-++--+ I've been doing some searching on the web and have no idea if/how this can be sped up. Most searches these days reference MySQL 5.x which I'm just not sure how much applies. I'm hoping that there is something obvious that I'm missing, or that one of you experts knows what I might be able to change to speed this query up. Anyhow, thanks in advance for even so much as reading my message, let alone replying :). You need to get rid of the GROUP BY to make this go faster. You can do that by running two queries, one to pick the list of unique recipients.message_id values that match your where condition then another to actually retrieve the message data. Something like this CREATE TEMPORARY TABLE tmpMessages (message_id INT UNSIGNED, PRIMARY KEY (message_id)) ENGINE=MEMORY; INSERT IGNORE tmpMessages SELECT message_id FROM recipients WHERE employee_id = X; SELECT messages.* FROM messages INNER JOIN tmpMessages ON tmpMessages.message_id = messages.id ORDER BY sent_at DESC LIMIT 0, 25; By pre-selecting a limited set of message_id values from the recipients table, you seriously reduce the number of rows that need to be scanned. Also, the INSERT IGNORE technique is faster than the GROUP BY because it uses an index to identify any duplicates instead of a scan of all previous unique values. Please let us all know if this is faster enough. (and don't forget to drop the temp table once you are through using it) -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN Thanks Shawn, I'm in the process of trying you're suggestion now. I'll let you know how
Re: how to generate a data set then join with in on fly?
Hi Ryan. That's a common issue for reporting. This post might have you an idea where to generate those inexistent dates (time slots), just forget about the specific aggregates and partitioning done in there: http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ -- Nuno Tavares DRI, Consultoria Informática Telef: +351 936 184 086 Shawn Green (MySQL) escreveu: On 1/10/2011 18:51, Ryan Liu wrote: Hi, In MySQL, is that possible to generate a data set join with it on fly (without create an temporary table)? e.g. for a report used by a graphic tool, it requires data in all dates, even it is null. Can I select vacationT.* left join ( all dates d in the past 3 years) on vacationT.`date` = d ? Thanks, Ryan Sorry, no. To do the report I think you are describing will require you to have a table of all dates. Also the date table needs to be on the LEFT side of the LEFT JOIN to be included even if there aren't any matches. SELECT FROM master_date_table LEFT JOIN vacationT ... Or, you can accept the partial list of dates actually stored in the database as accurate and fill in any missing dates when you render it in your report (inside the application). It may be much easier to fill-in those dates when you format the report, have you checked? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to generate a data set then join with in on fly?
On 1/10/2011 18:51, Ryan Liu wrote: Hi, In MySQL, is that possible to generate a data set join with it on fly (without create an temporary table)? e.g. for a report used by a graphic tool, it requires data in all dates, even it is null. Can I select vacationT.* left join ( all dates d in the past 3 years) on vacationT.`date` = d ? Thanks, Ryan Sorry, no. To do the report I think you are describing will require you to have a table of all dates. Also the date table needs to be on the LEFT side of the LEFT JOIN to be included even if there aren't any matches. SELECT FROM master_date_table LEFT JOIN vacationT ... Or, you can accept the partial list of dates actually stored in the database as accurate and fill in any missing dates when you render it in your report (inside the application). It may be much easier to fill-in those dates when you format the report, have you checked? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to generate a data set then join with in on fly?
Hi, In MySQL, is that possible to generate a data set join with it on fly (without create an temporary table)? e.g. for a report used by a graphic tool, it requires data in all dates, even it is null. Can I select vacationT.* left join ( all dates d in the past 3 years) on vacationT.`date` = d ? Thanks, Ryan
Re: join query for sale report
any suggestion, for my question plz On Fri, Dec 24, 2010 at 10:57 PM, bharani kumar bharanikumariyer...@gmail.com wrote: I want to take the sale report, Group by catID , Daily report , table name : tblbasket BID Auto Incre, Prim Key BID int(20), BasketSessionID varchar(100),ProductCode varchar(50), CatID int(10), Quantity int(20), AfterDiscount double,purchasedate datetime, Status int(3) table name : tblsale SaleID Auto Incre, Prim Key SaleID int(20), BillNo varchar(30), BasketSessionID varchar(200), CatID int(10), AfterDiscount double, VAT int(20),purchasedate datetime, Status int(2) the above is my table structure, I want to build two query , One query should display the daily sale report ,(for this i have purchasedate, AfterDiscount nothing but an bill amount, Status once the billing sucess then tblbasket status goes to 3 and tblsale Status goes to 1, On both table relationship is only BasketSessionID ) second query, this query should display the sale report group by cat id with VAt(vat is present in sale table, ) How to create the query ,For this scenario and another is -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
join query for sale report
I want to take the sale report, Group by catID , Daily report , table name : tblbasket BID Auto Incre, Prim Key BID int(20), BasketSessionID varchar(100),ProductCode varchar(50), CatID int(10), Quantity int(20), AfterDiscount double,purchasedate datetime, Status int(3) table name : tblsale SaleID Auto Incre, Prim Key SaleID int(20), BillNo varchar(30), BasketSessionID varchar(200), CatID int(10), AfterDiscount double, VAT int(20),purchasedate datetime, Status int(2) the above is my table structure, I want to build two query , One query should display the daily sale report ,(for this i have purchasedate, AfterDiscount nothing but an bill amount, Status once the billing sucess then tblbasket status goes to 3 and tblsale Status goes to 1, On both table relationship is only BasketSessionID ) second query, this query should display the sale report group by cat id with VAt(vat is present in sale table, ) How to create the query ,For this scenario and another is
GROUP BY - INNER JOIN and LIMIT - how to get result
I have tables: CREATE TABLE `tblNames` ( ` IdName` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(60) DEFAULT NULL, PRIMARY KEY (`IdName`), ) ENGINE=MyISAM CREATE TABLE `tblStatusy` ( `IdStatus` int(11) NOT NULL AUTO_INCREMENT, `IdName` int(11) DEFAULT NULL, `Status` varchar(60) DEFAULT NULL, `Data` datetime DEFAULT NULL, PRIMARY KEY (`IdStatus`), KEY `ixIDName` (`IdName `) ) ENGINE=MyISAM How to get result which will be look like this: tblNames.Id, tblNames.Name, (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1,1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 2,1) Any idea how to get this? Best regards
Re: How do I use and JOIN the mysql.time_zone% tables?
Part of your answer is the offset column, which seems to be relative to the abbreviation used. This implies, to me, that each particular abbreviation has it's own way of specifying the starting point of the time. Added is the DST flag, which (probably) tells you that your app needs to keep daylight savings time in mind. I can't tell you why there are so many, or which one to pick, but there you go. Just, you know, pick one, learn it's rules and stick to it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: How do I use and JOIN the mysql.time_zone% tables?
Interesting question -- I hope someone can give an in-depth explanation. I've created some TZ processing to use in Access, to go back and forth from local time to UTC (which Access doesn't make easy). To do this, I had to find and reformat some official files so I could cram them into Access tables. I learned a lot doing that, and I'm trying to relate this all to the TZ tables in MySQL. The tables I wound up with were: ISO3166_countries: Country Code (the ISA standard abbreviation for the country, two alpha characters) Country Name (the ISO standard name of the country) FIPS_regions: Country Code (same as ISO3166 Country Code) Region Code (identifies a part of a country, not unique across countries) Time Zone ID Time Zones: Time Zone ID Time Zone Name (ISO standard, I thought, but now I'm not so sure -- see below) Time Zones Data: ID (record identifier), not part of the official data Time Zone ID **multiple records per** Time Zone Start (seconds before or after the start of the UNIX epoch) GMT offset (seconds) DST (true/false) The reason that `Time Zones Data` has multiple records per `Time Zone ID` is that the rules for a particular zone might (probably did) change throughout history. The best **rough** equivalence to the tables in MySQL seems to be `Time Zones` - `mysql`.`time_zone_name` `Time Zones Data` - `mysql`.`time_zone_transition` but I don't know what `transition_type` means, and the time zone names don't match what I thought were the ISO standards. For example, the data I found when gathering my data has time zone 106 as America/New_York, whereas the MySQL table `time_zone_name` has time zone 106 as America/Fort_Wayne. Those two cities are not in the same time zone. I have another question for the group: when are the time zone tables updated? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Wednesday, October 13, 2010 10:51 PM To: mysql@lists.mysql.com Subject: How do I use and JOIN the mysql.time_zone% tables? I'm trying to figure out how to join the mysql.time_zone% tables and make sense of this. YES, I know how to use them with SET time_zone = timezone; and all that. http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html That is NOT what I need them for (yet). I have a list of airports and those airports have cities and countries. I need to correlate those cities (which may or may not have an exact match in the time_zone_name table, so for each airport/city, I will need to hunt down the right offset via some page like this: http://www.timeanddate.com/worldclock/search.html THEN store the Time_zone_id in my city table (or whatever the unique combination is that I'd need and at this point I'm very confused as to what that is). The problem is I can find no real documentation what all these 5 tables are each for (some are obvious, but so cryptic it's hard to digest them) Moreover, I don't understand the results I'm getting... SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%'; Name Time_zone_id - Europe/Brussels 412 posix/Europe/Brussels 993 right/Europe/Brussels 1574 First, WTF are there THREE Brussels?? SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993, 1574); Time_zone_id Transition_type_id Offset Is_DST Abbreviation -- -- -- 412 0 0 0 WET 412 13600 0 CET 412 27200 1 CEST 412 33600 0 CET 412 47200 1 CEST 412 53600 1 WEST 412 6 0 0 WET 412 7 0 0 WET 412 87200 1 CEST 412 93600 0 CET 993 0 0 0 WET 993 13600 0 CET 993 27200 1 CEST 993 33600 0 CET 993 47200 1 CEST 993 53600 1 WEST 993 6 0 0 WET 993 7 0 0 WET 993 87200 1 CEST 993 93600 0 CET 1574 0 0 0 WET 1574 13600 0 CET 1574 27200 1 CEST 1574 33600 0 CET 1574 4
How do I use and JOIN the mysql.time_zone% tables?
I'm trying to figure out how to join the mysql.time_zone% tables and make sense of this. YES, I know how to use them with SET time_zone = timezone; and all that. http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html That is NOT what I need them for (yet). I have a list of airports and those airports have cities and countries. I need to correlate those cities (which may or may not have an exact match in the time_zone_name table, so for each airport/city, I will need to hunt down the right offset via some page like this: http://www.timeanddate.com/worldclock/search.html THEN store the Time_zone_id in my city table (or whatever the unique combination is that I'd need and at this point I'm very confused as to what that is). The problem is I can find no real documentation what all these 5 tables are each for (some are obvious, but so cryptic it's hard to digest them) Moreover, I don't understand the results I'm getting... SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%'; Name Time_zone_id - Europe/Brussels 412 posix/Europe/Brussels 993 right/Europe/Brussels 1574 First, WTF are there THREE Brussels?? SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993, 1574); Time_zone_id Transition_type_id Offset Is_DST Abbreviation -- -- -- 412 0 0 0 WET 412 13600 0 CET 412 27200 1 CEST 412 33600 0 CET 412 47200 1 CEST 412 53600 1 WEST 412 6 0 0 WET 412 7 0 0 WET 412 87200 1 CEST 412 93600 0 CET 993 0 0 0 WET 993 13600 0 CET 993 27200 1 CEST 993 33600 0 CET 993 47200 1 CEST 993 53600 1 WEST 993 6 0 0 WET 993 7 0 0 WET 993 87200 1 CEST 993 93600 0 CET 1574 0 0 0 WET 1574 13600 0 CET 1574 27200 1 CEST 1574 33600 0 CET 1574 47200 1 CEST 1574 53600 1 WEST 1574 6 0 0 WET 1574 7 0 0 WET 1574 87200 1 CEST 1574 93600 0 CET Now WTF are there TEN rows PER? This page: http://www.timeanddate.com/worldclock/city.html?n=48 says Brussels, Belgium is CEST so why are there 10 time_zone_transition_types SELECT * FROM time_zone_name JOIN time_zone_transition_type ON time_zone_name.Time_zone_id = time_zone_transition_type.Time_zone_id -- AND time_zone_transition.Transition_type_id = -- time_zone_transition_type.Transition_type_id WHERE `Name` LIKE '%brussels%'; Name Time_zone_id Transition_type_id Offset Is_DST Abbreviation - -- -- -- Europe/Brussels 412 0 0 0 WET Europe/Brussels 412 13600 0 CET Europe/Brussels 412 27200 1 CEST Europe/Brussels 412 33600 0 CET Europe/Brussels 412 47200 1 CEST Europe/Brussels 412 53600 1 WEST Europe/Brussels 412 6 0 0 WET Europe/Brussels 412 7 0 0 WET Europe/Brussels 412 87200 1 CEST Europe/Brussels 412 93600 0 CET posix/Europe/Brussels 993 0 0 0 WET posix/Europe/Brussels 993 13600 0 CET posix
Re: Add record number to timestamped router data to facilitate cross join
On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy djstu...@gmail.com wrote: All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1)) Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +--+--+--+--+--+ | routerID | timestamp| counter1 | counter2 | counter3 | +--+--+--+--+--+ |1 | 24/08/2010 10:36 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 36 | 75 | 31 | +--+--+--+--+--+ My plan, to facilitate the cross join, was to add a per-device record number like follows: +--+--+---+--+--+--+ | routerID | timestamp| recordNum | counter1 | counter2 | counter3 | +--+--+---+--+--+--+ |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +--+--+---+--+--+--+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Hey all, Anyone have any thoughts/advice on the best way to manage this record number? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.com
RE: Add record number to timestamped router data to facilitate cross join
I don't think I'd seriously consider the max() option, especially if you're expecting the table to grow large. Using a table to store the next record number (your last_used_record_nums table) is a technique that is fairly commonly used. You can increment and retrieve the value atomically if you use the LAST_INSERT_ID(): insert into last_used_record_nums(routerid, recordnum) values (, last_insert_id(1)) on duplicate key update recordnum = last_insert_id(recordnum + 1); then select last_insert_id(); to get the incremented value. You could probably wrap this in an INSERT trigger on your stats table so your application wouldn't need to worry about it. -Travis -Original Message- From: Jake Peavy [mailto:djstu...@gmail.com] Sent: Tuesday, October 05, 2010 10:34 AM To: MySQL General Mailing List Subject: Re: Add record number to timestamped router data to facilitate cross join On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy djstu...@gmail.com wrote: All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1)) Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +--+--+--+--+--+ | routerID | timestamp| counter1 | counter2 | counter3 | +--+--+--+--+--+ |1 | 24/08/2010 10:36 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 36 | 75 | 31 | +--+--+--+--+--+ My plan, to facilitate the cross join, was to add a per-device record number like follows: +--+--+---+--+--+--+ | routerID | timestamp| recordNum | counter1 | counter2 | counter3 | +--+--+---+--+--+--+ |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +--+--+---+--+--+--+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Hey all, Anyone have any thoughts/advice on the best way to manage this record number? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Add record number to timestamped router data to facilitate cross join
All, I have a number of routers which report in with various stats periodicially. This period is not a regular interval and can drift based on other factors. Each router drifts independently. The stats the routers provide need to be analyzed in terms of deltas between reports (rather than the absolute number). Therefore I need to perform a cross join to compare the rows for a given routerID (something like SELECT r1.timestamp, r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON (r2.recordNum = r1.recordNum + 1)) Here's an example of some raw data to give you an idea showing 3 records each from 2 devices: +--+--+--+--+--+ | routerID | timestamp| counter1 | counter2 | counter3 | +--+--+--+--+--+ |1 | 24/08/2010 10:36 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 36 | 75 | 31 | +--+--+--+--+--+ My plan, to facilitate the cross join, was to add a per-device record number like follows: +--+--+---+--+--+--+ | routerID | timestamp| recordNum | counter1 | counter2 | counter3 | +--+--+---+--+--+--+ |1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 | |2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 | |2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 | |1 | 24/08/2010 12:41 | 2 | 4| 84 | 82 | |1 | 24/08/2010 14:58 | 3 | 26 | 9| 62 | |2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 | +--+--+---+--+--+--+ So here's my question, first, of course, is there a better way to perform the cross join? If not, what's the easiest way to add and increment the recordNum field? Can I do it directly in SQL somehow? Or do I need to do it in my parser? If I do it in my parser, it runs periodically (as it receives the reports) so I think it would need to figure out what record number it assigned to which device last so it would know where to restart the numbering. Should I hold that in the parser itself, or a separate table (SELECT routerID,last_used_record_num FROM last_used_record_nums; then parse, incrementing record num, then write the last ones back to that table) or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM router_data GROUP BY routerID)? My only concern with the last approach is that router_data is going to get very large and that query may get very slow. TIA for any advice, Tks, -- -jp I wish everybody would have to have an electric thing implanted in our heads that gave us a shock whenever we did something to disobey the president. Then somehow I get myself elected president. deepthoughtsbyjackhandey.com
Conditional join of tow tables
Hi listers mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Conditional join of tow tables
Does this work? select * from t1 join t2 on (t1.datum = t2.sdat or dayname(t1.datum) = t2.tag); -Travis -Original Message- From: mysql [mailto:my...@ayni.com] Sent: Tuesday, September 07, 2010 1:43 AM To: mysql@lists.mysql.com Subject: Conditional join of tow tables Hi listers mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Conditional join of tow tables
Hi Travis Thank you for the hint. i yesterday found the following hint, which I then followed. select t1.datum, t2.sdat. t3.tag from table as t1 left outer join table2 as t2 on t1.datum = t2.sdat left outer join table2 as t3 on dayname(t1.datum) = t3.tag Note: it does not work with inner joins, you have to take outer joins. suomi On 2010-09-07 21:21, Travis Ard wrote: Does this work? select * from t1 join t2 on (t1.datum = t2.sdat or dayname(t1.datum) = t2.tag); -Travis -Original Message- From: mysql [mailto:my...@ayni.com] Sent: Tuesday, September 07, 2010 1:43 AM To: mysql@lists.mysql.com Subject: Conditional join of tow tables Hi listers mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 5.1.46 | +---++ 1 row in set (0.02 sec) mysql Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql describe schulung; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | sdat | date | YES | | NULL|| | tag | text | YES | MUL | NULL|| | szeit| time | YES | | NULL|| | speziell | text | YES | | NULL|| | id | int(10) unsigned | NO | PRI | NULL| auto_increment | +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag) but I got an ERROR 1064 near 'not exists (select sdat ' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Nested join query?
Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Nested join query?
I think you could do away with your right outer join of table B (which will include all rows from B whether or not they match to C), since you are explicitly filtering for C.State like 'Yes'. The intermediate result doesn't necessarily need to be stored in a temporary table. You can include multiple tables in a single query by specifying each table and the join condition. Also, If you just want a distinct list of values, you could use the DISTINCT clause as opposed to grouping. Something like the following query should produce the results you're looking for: select distinct a.val from tablec c inner join tableb b on b.id = c.id inner join tablea a on a.num = b.num where c.state = 'Yes'; -Travis -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, August 30, 2010 4:39 PM To: MySql Subject: Nested join query? Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
