RE: Finding gaps
Thanks for the suggestion. Unfortunately that doesn't fit my need, because I need to go back in time. From: Moon's Father [mailto:[EMAIL PROTECTED] Sent: Saturday, October 25, 2008 2:57 AM To: Jerry Schwartz Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Finding gaps Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database. On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Finding gaps
Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database. On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Finding gaps
Or you can create a temporary table including a auto_increment attribute and fill it with continuous numbers. Then simply use it to left join the original table. On Sat, Oct 25, 2008 at 2:57 PM, Moon's Father [EMAIL PROTECTED]wrote: Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database. On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
RE: Finding gaps
Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
RE: Finding gaps
I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; SELECT a.id+1 AS `Missing_From`, MIN(b.id) - 1 AS `To` FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id `To` -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
-Original Message- From: Gerald L. Clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 4:44 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; SELECT a.id+1 AS `Missing_From`, MIN(b.id) - 1 AS `To` FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id `To` [JS] That didn't seem to work: mysql SELECT a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num) - 1 AS `To` - FROM prod AS a, prod AS b - WHERE a.prod_num b.prod_num - GROUP BY a.prod_num - HAVING a.prod_num `To`; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
RE: Finding gaps
Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
Re: Finding gaps
HI ! Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Hi ! Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
On 18 Sep 2008, at 07:45, Joerg Bruehe wrote: Stut schrieb: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. I don't know his application, but I do remember a similar requirement caused by some German rules on bookkeeping which demanded that booking numbers were assigned without gaps. That law may be illogical, but for the DB application designer this doesn't help - s/he has no choice but to follow it. Autonumber will accomplish that, so long as you don't delete any. And if you do, renumbering the bookings would cause more problems than it solved. This reminds me of when my parents used to make me account for every cheque I'd written, and they did it by ensuring I had a reason for each sequentially numbered cheque in the cheque book. Any I had written and then destroyed had to be marked as destroyed - it couldn't actually be missing!! I see the same issue here and the same solution works. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
On Thu, 2008-09-18 at 09:58 +0100, Stut wrote: Autonumber will accomplish that, so long as you don't delete any. And if you do, renumbering the bookings would cause more problems than it solved. Autonumber has the possibility of gaps. When a record is insert, the counter is incremented. It is possible that a computer crash will stop the transaction after the increment but before the record is stored. Whatever record is inserted next will result in a gap. Either you have uniqueness and the possibility of gaps or no gaps and the possibility of duplicates. This is true even if the system is completely manual. That's just the way the universe is made. And yes, if you re-number, you introduce the possibility of duplicates. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Cross Time Cafe Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster There is more than one way to do things. A Perl axiom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
-Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
Yes, that would have been a very good idea. I did not design this. Even if we used auto-increment, my current problem would be the same: finding gaps in the numbering. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:29 PM To: Stut; Jerry Schwartz Cc: mysql@lists.mysql.com Subject: RE: Finding gaps unless you cant spare a few milliseconds off of each insert.. i strongly suggest to use autoincrement http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: mysql@lists.mysql.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Finding gaps Date: Wed, 17 Sep 2008 22:16:52 +0100 On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ See how Windows Mobile brings your life together-at home, work, or on the go. See Now http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
Re: Finding gaps
Hi all, I'm just throwing something out ... How about: select a.id,b.id from dataset a left join dataset b on a.id=b.id+1 where b.id is null; This should find single gaps. It won't find larger gaps. Just my $.02. Mike. On Thursday 18 September 2008 10:44:47 am Jerry Schwartz wrote: -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- Mike Diehl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
Alas, the gaps are as large as 500. Normally, products are never deleted from the system; but I put in some corrupt data that I did not want to pass along, even if I marked them as discontinued. They complain about that, too. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Diehl [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2008 1:17 PM To: mysql@lists.mysql.com Cc: Jerry Schwartz; 'Stut' Subject: Re: Finding gaps Hi all, I'm just throwing something out ... How about: select a.id,b.id from dataset a left join dataset b on a.id=b.id+1 where b.id is null; This should find single gaps. It won't find larger gaps. Just my $.02. Mike. On Thursday 18 September 2008 10:44:47 am Jerry Schwartz wrote: -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 6:30 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? [JS] In this case, we are dealing with a list of products. If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. [JS] I can pass along my boss's email address, if you want to explain to him why it doesn't matter. Personally, I depend upon my job. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. [JS] They are propagated into other databases that I do not control. They are managed and used by our main office in Japan. They notice everything (except misspellings). -- Mike Diehl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding gaps
I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Re: Finding gaps
On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM
RE: Finding gaps
Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:17 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding gaps
unless you cant spare a few milliseconds off of each insert.. i strongly suggest to use autoincrement http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: mysql@lists.mysql.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Finding gaps Date: Wed, 17 Sep 2008 22:16:52 +0100 On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ See how Windows Mobile brings your life together—at home, work, or on the go. http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
Re: Finding gaps
On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. -Stut -- http://stut.net/ -Original Message- From: Stut [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:17 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Finding gaps On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
On Wed, 2008-09-17 at 23:29 +0100, Stut wrote: On 17 Sep 2008, at 22:34, Jerry Schwartz wrote: Our Japanese partners will notice and will ask. Similar things have come up before. I want to be pro-active. Notice what? Why would it be bad? What type of data are we dealing with here? If each row requires a unique ID use an autonumber. If your partners don't understand that deleted items will create gaps, explain it to them. IMHO you're creating a problem that doesn't exist. If you just need sequential numbers for display purposes, generate them when you do the displaying. There's no need for those numbers to be in the database. -Stut More than that: if you want to guarantee that the IDs will be unique, there is the possibility of gaps. If there are no gaps, there is the possibility of two (or more) items having the same ID. This is true even if you re-number everything. They have a choice: uniqueness and gaps, or no gaps and non-uniqueness. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Cross Time Cafe Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster There is more than one way to do things. A Perl axiom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing an answer to an topic (Finding gaps in db)
At 4:54 +0200 9/10/02, Jan Broermann wrote: Hi, I'm missing an answer to a topic which came up a couple of days /weeks ago. For a database i'm administrating I would like to find out, which numbers (of invoices) are missing in our database. Is there a way to get this result set thru SQL? Or do I have to do it with for example Java? I think somebody else asked a question similar to my probleme, but I can't find an answer in my mails. I think it must be a construction of something like not in between [max]max(tab.invoice) and [min] min (tab,invoice) You need a reference table that lists all the invoice IDs in the range that you want to check. Then you can perform a left join of this table against your invoice table to find non-matching records. If you have no such reference, then you can select the IDs that do happen to lie within the range, and apply some programming logic to figure out which ones aren't there. thx from Germany Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Missing an answer to an topic (Finding gaps in db)
Hi, If you wish to find NUMBERS which are missing, i think you can try this: set @a:=0;/*initialise variable a*/ select @a:=@a+1,IF(YOUR_COLUMN_INT_TYPE= @a,'OK',@a:= @a+1) FROM YOUR TABLE; Don't forget to initialise variable 'a' for every time when run the second query. This is work indeed but if you want to have a definitively solution i think you should to do an UDF where you can make some rules and conditions to checking what data is missing from the table field(column). Hope it's help Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Jan Broermann [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 10, 2002 6:01 AM Subject: Missing an answer to an topic (Finding gaps in db) Hi, I'm missing an answer to a topic which came up a couple of days /weeks ago. For a database i'm administrating I would like to find out, which numbers (of invoices) are missing in our database. Is there a way to get this result set thru SQL? Or do I have to do it with for example Java? I think somebody else asked a question similar to my probleme, but I can't find an answer in my mails. I think it must be a construction of something like not in between [max]max(tab.invoice) and [min] min (tab,invoice) thx from Germany Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Missing an answer to an topic (Finding gaps in db)
Hi, I'm missing an answer to a topic which came up a couple of days /weeks ago. For a database i'm administrating I would like to find out, which numbers (of invoices) are missing in our database. Is there a way to get this result set thru SQL? Or do I have to do it with for example Java? I think somebody else asked a question similar to my probleme, but I can't find an answer in my mails. thx from Germany Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Missing an answer to an topic (Finding gaps in db)
Hi, I'm missing an answer to a topic which came up a couple of days /weeks ago. For a database i'm administrating I would like to find out, which numbers (of invoices) are missing in our database. Is there a way to get this result set thru SQL? Or do I have to do it with for example Java? I think somebody else asked a question similar to my probleme, but I can't find an answer in my mails. I think it must be a construction of something like not in between [max]max(tab.invoice) and [min] min (tab,invoice) thx from Germany Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Missing an answer to an topic (Finding gaps in db)
Hi, I'm missing an answer to a topic which came up a couple of days /weeks ago. For a database i'm administrating I would like to find out, which numbers (of invoices) are missing in our database. Is there a way to get this result set thru SQL? Or do I have to do it with for example Java? I think somebody else asked a question similar to my probleme, but I can't find an answer in my mails. I think it must be a construction of something like not in between [max]max(tab.invoice) and [min] min (tab,invoice) thx from Germany Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Finding gaps in date intervals
Hi, I have a table with this structure: CREATE TABLE dats ( id INT UNSIGNED NOT NULL, id_ref INT UNSIGNED NOT NULL, start DATE NOT NULL, endDATE NOT NULL ) Now I am trying to create a query that will give me all intervals for which the table does not contain an entry grouped by id_ref, e.g. if the table would contain 1, 1, '2000-01-01', '2000-31-12' 2, 1, '2001-02-01', '2002-31-12' I would like to get 1, '2001-01-01', '2001-01-31', as there is a gap between these two entries. Any idea on how to get this? Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
