Neil, Let's extend Kalok's suggestion: A well-designed database is supposed to contain data that reflects/represents a 'reality'. The telephone extension guide describes a 'reality' of the PBX as viewed by the users - a telephone list by any other name - built to answer the question "how do I phone Fred?" with only slight variants, eg putting all of one department's numbers into a single sub-list together.
However asking the question "what about finding the extensions that are not allocated?" is asking a question that does not appear in that view of reality! Rather than addressing this as an issue for SQL, why not correct the design/reality? The question you are asking is related to the reality of the PBX - all of the circuits it provides, to whom each is allocated, and further categorisation of those people/allocations - and the fact that some of them are not (currently) allocated. To accomplish this, use INSERT*** to update the table to include every extension number that is available on the PBX, but leave the extension allocation data empty until the number is activated. When you implement this query to find 'available' extensions, it becomes trivial. When a telephone list is to be produced, the query also remains trivial/as it is today, but for the addition of a simple WHERE...IS NOT NULL condition. *** consider making that column UNIQUE, and if you've not come across it before, check out INSERT IGNORE in the manual. Regards, =dn ----- Original Message ----- From: "Kalok Lo" <[EMAIL PROTECTED]> > 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]> > > > 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
