And assuming the number ranges do not overlap or you may count rows twice.

Paul

-----Original Message-----
From: Ball, David [mailto:David.Ball@npower.com]
Sent: Wednesday, 22 June 2005 10:35 PM
To: 'Nagsy'; info-ingres@cariboulake.com
Subject: RE: [Info-ingres] Need help with query....

Nagsy,

Does this do the job?

select count (*)
from range r, numbers n
where n.number between r.low_number and r.high_number
and r.id = '01'
;

HTH
Dave

-----Original Message-----
From: Nagsy [mailto:nagsy101@yahoo.co.uk]
Sent: 22 June 2005 12:21
To: info-ingres@cariboulake.com
Subject: [Info-ingres] Need help with query....

Hello all,

I would like some help with a sql query - the related tables are as
follows:

range table:
low_number      high_number     id
0832000000     |0833000000     |01|
00832000000    |00833000000    |02|
000832000000   |000833000000   |01|
0000832000000  |0000833000000  |01|
00000832000000 |00000833000000 |01|
000000832000000|000000833000000|01|

numbers table:
number
0832000001
0832000002
0832000003
0832000004
0832000005
0832000006
0832000007
0832000008
0832000009
0832000010
00832000051
00832000052
00832000053
00832000054
00832000055
00000832000601
00000832000602
00000832000603
00000832000604
00000832000605
(20 rows)

select count(*) from numbers
where number >=
   (select min(low_number) from range
   where id = '01')
and number <=
   (select max(high_number) from range
   where id = '01')

The above select returns 20 records however I only want the numbers
that are in the number range and have an associated id of '01' is this
possible? i.e. there should be no numbers in the range of 00832000000
to 00833000000 included in the results.

There are likely to be other ranges in the range table and a greater
number of id's and numbers.

Please advise

Cheers,

Nagsy

_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

**********************************************************************

The information contained in this e-mail is confidential and intended only
for the use of the addressee. If the reader of this message is not the
addressee, you are hereby notified that you have received this e-mail in
error and you must not copy, disseminate, distribute, use or take any action
as a result of the information contained in it.
   
If you have any queries, please contact the IT Service Desk on 1870
(01384-275454).
   
postmaster@npower.com

**********************************************************************

This e-mail is provided for general information purposes only and does not
constitute investment or transactional advice. For  the avoidance of doubt
the contents of this email are subject to contract and will not constitute a
legally binding contract.

The information contained in this e-mail is confidential and intended only
for the use of the addressee. If the reader of this message is not the
addressee, you are hereby notified that you have received this e-mail in
error and you must not copy, disseminate, distribute, use or take any action
as a result of the information contained in it.
   
If you have received this e-mail in error, please notify
postmaster@npower.com (UK 01384 275454) and delete it immediately from your
system.
   
Neither Npower nor any of the other companies in the RWE Npower group from
whom this e-mail originates accept any responsibility for losses or damage
as a result of any viruses and it is your responsibility to check
attachments (if any) for viruses.
Npower Limited Registered office: Windmill Hill Business Park, Whitehill
Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277.  This
e-mail may be sent on behalf of a member of the RWE Npower group of
companies.

**********************************************************************

_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Hi David,

The suggested query returns a cartesian product - I only want a count
of the number of related records in the numbers table.

Any ideas?