Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd7cy2so!shaw.ca!news-out1.nntp.be!propagator2-sterling!news-in-sterling.newsfeed.com!ptdnetS!newsgate.ptd.net!newsfeed.mathworks.com!fu-berlin.de!uni-berlin.de!p508d7186.dip.t-dialin.NET!not-for-mail From: Joachim Zobel Newsgroups: comp.databases.oracle.server Subject: Re: Select between range, not in... Date: Fri, 12 Dec 2003 23:50:47 +0100 Lines: 33 Message-ID: References: <9849ae9d.0312120948.36f1c223@posting.google.com> NNTP-Posting-Host: p508d7186.dip.t-dialin.net (80.141.113.134) Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit X-Trace: news.uni-berlin.de 1071269448 2385306 80.141.113.134 ([248]) User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity. (Debian GNU/Linux)) Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:250029 On Fri, 12 Dec 2003 09:48:44 -0800, Pret Orian wrote: > Hi there! > I have a table with used IP Adresses, and I have a table with > subnetworks (with IP range: start/end). > I need to make a select statement which would return the first free IP > in range. > The catch is I'd need to find a solution that would use basic SQL > query as I have no permission to write SPs or functions. > > So this would be the table structures: > > Subnet: > sune_id, ipstart, ipend, ... > > IPs: > ..., sune_id, ipadress These data have a consistency condition: SELECT * FROM subnet s, ips i WHERE s.sune_id=i.sune_id AND i.ipadress NOT BETWEEN s.ipstart AND s.ipend should return no rows. Does it? Sincerely, Joachim -- Warnung: \" kann Augenkrebs verursachen.