Jack wrote:
Quote:
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'
