JOIN between 2 tables. No foreign key, just ranges

[Jack]

Hi,

I want to "join" 2 tables. In the second table, there is no foreign
key. The main table contains ranges (From... to...) that are used to
select rows in the second table.

1) SQL script for main table BHCODE
CREATE TABLE BHCODE
(CODE CHAR ( 3) NOT NULL WITH DEFAULT,
SEQ DEC ( 4) NOT NULL WITH DEFAULT,
FROMACCT DEC ( 4) NOT NULL WITH DEFAULT,
TOACCT DEC ( 4) NOT NULL WITH DEFAULT,
PRIMARY KEY (CODE,
SEQ))

2) SQL script for detail table BHMAIN
CREATE TABLE BHACCT
(ACCT DEC (4 ) NOT NULL WITH DEFAULT,
DESC CHAR (30 ) NOT NULL WITH DEFAULT,
PRIMARY KEY
(ACCT))

3) Here some data in table BHCODE
CODE SEQ FROMACCT TOACCT
A 1 1 2
A 2 3 4
A 3 6 7
B 1 10 11
B 2 12 13

4) Here some data in table BHACCT
ACCT DESC
1 Account 1
2 Account 2
3 Account 3
4 Account 4
5 Account 5
6 Account 6
7 Account 7
8 Account 8
9 Account 9
10 Account 10
11 Account 11
12 Account 12
13 Account 13

What I would like to do: I would like to retrieve rows from BHACCT
where CODE in BHCODE = given value.

IE: I would like to get the accounts 1 to 7 (excluding 5) from BHACCT
when column BHCODE.CODE = 'A'

I don't know how to build my SQL request to get this result. I'm not
even sure this can be done with SQL. FYI, there tables are already
existing and cannot be modified, thanks to our legacy systems...

Thanks for any help

Jack

[Ian]

Jack wrote:

Hi,

I want to "join" 2 tables. In the second table, there is no foreign
key. The main table contains ranges (From... to...) that are used to
select rows in the second table.

1) SQL script for main table BHCODE
CREATE TABLE BHCODE
(CODE CHAR ( 3) NOT NULL WITH DEFAULT,
SEQ DEC ( 4) NOT NULL WITH DEFAULT,
FROMACCT DEC ( 4) NOT NULL WITH DEFAULT,
TOACCT DEC ( 4) NOT NULL WITH DEFAULT,
PRIMARY KEY (CODE,
SEQ))

2) SQL script for detail table BHMAIN
CREATE TABLE BHACCT
(ACCT DEC (4 ) NOT NULL WITH DEFAULT,
DESC CHAR (30 ) NOT NULL WITH DEFAULT,
PRIMARY KEY
(ACCT))

3) Here some data in table BHCODE
CODE SEQ FROMACCT TOACCT
A 1 1 2
A 2 3 4
A 3 6 7
B 1 10 11
B 2 12 13

4) Here some data in table BHACCT
ACCT DESC
1 Account 1
2 Account 2
3 Account 3
4 Account 4
5 Account 5
6 Account 6
7 Account 7
8 Account 8
9 Account 9
10 Account 10
11 Account 11
12 Account 12
13 Account 13

What I would like to do: I would like to retrieve rows from BHACCT
where CODE in BHCODE = given value.

IE: I would like to get the accounts 1 to 7 (excluding 5) from BHACCT
when column BHCODE.CODE = 'A'

Not really sure what you mean -- do you just want accounts that exist
in *either* the FROMACCT or TOACCT columns?

select a.acct, a.desc
from bhacct a, bhcode c
where c.fromacct = a.acct
and c.bhcode = 'A'
union all
select a.acct, a.desc
from bhacct a, bhcode c
where c.toacct = a.acct
and c.bhcode = 'A'
Or do fromacct / toacct specify a range of account numbers (i.e.
fromacct = start of range, toacct = end of range)?

select a.acct, a.desc
from bhacct a, bhcode c
where a.acct between c.fromacct and c.toacct
and c.code = 'A'

[Tonkuma]

You can use new join syntax with BETWEEN predicate.

SELECT at.*
FROM bhcode cd
JOIN bhacct at
ON cd.code = 'A'
AND at.acct BETWEEN cd.fromacct AND cd.toacct
;

[Jack]

Thanks guys! It works fine!!!

On 18 nov, 02:39, Tonkuma <[e-mail]:

You can use new join syntax with BETWEEN predicate.

SELECT at.*
* FROM bhcode cd
* JOIN bhacct at
* *ON *cd.code = 'A'
* *AND at.acct BETWEEN cd.fromacct AND cd.toacct
;