you could possibly do this. well first, is there a fix size for the extension ? i.e 3 digit or 4 digits. if so, you could easily fill a table with a script with all the ranges of extensions, say that table is called static_extension
then you can do the "not in" join select * from static_extension_table a left join your_table b on a.ext_column = b.ext_column and a.ext_column is null; ----- Original Message ----- From: "Neil Silvester" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 31, 2002 7:03 PM Subject: Missing values in an INT type column > I hope this doesn't sound like a stupid question. > > Is there anyway of finding missing values in an INT column type? > Say I have a telephone extension guide. Using the MIN() and MAX() functions > I am able to find the start and end ranges. But what about finding the > extensions that are not allocated. > At the moment I am thinking I will need to use an external script that will > query the individual entries in the array of my returned result (SELECT > extensions FROM table ORDER BY extensions). If the difference between > consecutive rows row[x] and row[y] is +1 (or negative depending on ascending > or descending ordering), then all is good. But if the result is +x, I will > need to echo an array to the screen of row[x] ++1 while row[x] < row[y]. > It will take some work, but shouldn't be too hard to implement. > I guess my question is, is there a function in MySQL that will do this, or > at least help me along with it, or has anyone come across a need for this > before. > > > Neil Silvester > Webmaster > Heat and Control > Ph: +61 7 3877 6429 > [EMAIL PROTECTED] > http://www.heatandcontrol.com > > > --------------------------------------------------------------------- > 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
