Hi all



Sorry for the cross postings but there may be a simple solution that can be
done in SQL.

I am trying to work out if there are missing numbers in a range stored in a
database table.



The first problem is the number range is prefixed with UR (eg UR12345), what
I need to

try and work out it are there any numbers in the range missing, the range
should be from

UR1000 to UR30000, what the best way to try and work this out?



Thanks



Brian





--------------------------------------------------------------------------------

I am using the free version of SPAMfighter for private users.
It has removed 1005 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

Brian wrote:
> Hi all
>
>
>
> Sorry for the cross postings but there may be a simple solution that can be
> done in SQL.
>
> I am trying to work out if there are missing numbers in a range stored in a
> database table.
>
>
>
> The first problem is the number range is prefixed with UR (eg UR12345), what
> I need to
>
> try and work out it are there any numbers in the range missing, the range
> should be from
>
> UR1000 to UR30000, what the best way to try and work this out?
>
>
>
> Thanks
>
>
>
> Brian
>
>

This is a bit off target for alt.php. But you could also try a
newsgroup for your database, i.e. comp.databases.(your_rdbms_here).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Brian wrote:
> Hi all
>
>
>
> Sorry for the cross postings but there may be a simple solution that can be
> done in SQL.
>
> I am trying to work out if there are missing numbers in a range stored in a
> database table.
>
>
>
> The first problem is the number range is prefixed with UR (eg UR12345), what
> I need to
>
> try and work out it are there any numbers in the range missing, the range
> should be from
>
> UR1000 to UR30000, what the best way to try and work this out?

As far as I know, you would need to do

for($i=1000;$i<=30000;$i++) {
$query="SELECT count(*) FROM table WHERE column='UR{$i}'";
$res=mysqli->query($query);
if(!$res->num_rows) {
echo "Missing number is UR{$i}\n";
}
}


--

//Aho

"Brian" <brianNOPSPAM@nrwp.co.uk> wrote in message
newszE7j.4685$745.1748@newsfe1-win.ntli.net...

> I am trying to work out if there are missing numbers in a range stored in
> a database table.

SELECT a.Id + 1 Missing
FROM foo a
LEFT JOIN foo b
ON b.Id = a.Id + 1
WHERE b.Id IS NULL

(that's one way...and faster than working over a resultset in php to get the
same result)

> The first problem is the number range is prefixed with UR (eg UR12345),
> what I need to
>
> try and work out it are there any numbers in the range missing, the range
> should be from
>
> UR1000 to UR30000, what the best way to try and work this out?

cast the result of right(a, length(a) - 2) as an integer and plug that into
the query above...changing the names to your appropriate tables and fields.

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:KaWdnaFtO9yOx8LanZ2dnUVZ_jednZ2d@comcast.com. ..
> Brian wrote:
>> Hi all
>>
>>
>>
>> Sorry for the cross postings but there may be a simple solution that can
>> be done in SQL.
>>
>> I am trying to work out if there are missing numbers in a range stored in
>> a database table.
>>
>>
>>
>> The first problem is the number range is prefixed with UR (eg UR12345),
>> what I need to
>>
>> try and work out it are there any numbers in the range missing, the
>> range should be from
>>
>> UR1000 to UR30000, what the best way to try and work this out?
>>
>>
>>
>> Thanks
>>
>>
>>
>> Brian
>>
>>
>
> This is a bit off target for alt.php. But you could also try a newsgroup
> for your database, i.e. comp.databases.(your_rdbms_here).

it looks like he's well aware of the ot-ishness of the post. can't you take
the whole 30 seconds it takes to write the damn thing! this ain't rocket
science you know.

"J.O. Aho" <user@example.net> wrote in message
news:5saj15F17fsctU1@mid.individual.net...

> for($i=1000;$i<=30000;$i++) {
> $query="SELECT count(*) FROM table WHERE column='UR{$i}'";
> $res=mysqli->query($query);
> if(!$res->num_rows) {
> echo "Missing number is UR{$i}\n";
> }
> }

aho, i'm not going to say that's a crazy way to do it...but, just look at
the resources you're wasting! you really want to run a query for *every*
iteration? even this is more attractive...but butt-ugly:

$sql = array();
for ($i = 1000; $i <= 3000; $i++)
{
$sql[] = "
SELECT 'UR" . $i . " Id ,
COUNT(*) Missing
FROM table
WHERE column = 'UR" . $i . "
";
}
$sql = "
SELECT *
FROM
(
" . implode(' UNION', $sql) . "
)
WHERE Missing = 0
";
$query = mysqli->query($sql);
echo '<pre>' . print_r($query, true) . '</pre>';