Creating tables with fields from 3 different tables

Question

I'm trying to group data from the following three tables to create a new table. the account_no fields in bmf and acct_map are actually drawn from cmf.

Fields:

bmf: account_no, trans_date
cmf: account_no, balance_due
acct_map: account_no, external_id

The fields I want in my new table are :

external_id, account_no, balance_due

When I use query 1, without the external_id column, it works fine and populates the data correctly. But when I try query 2, it creates a blank table. Why is this happening?

Query 1:

create table paid as
select bmf.account_no, sum(cmf.balance_due) postpaid_balance
from bmf, cmf
where to_char(bmf.trans_date, 'YYYY MM DD') = '1996 08 14' and
bmf.account_no = cmf.account_no
group by bmf.account_no

Query 2:

create table paid as
select bmf.account_no, sum(cmf.balance_due) postpaid_balance, acct_map.external_id
from bmf, cmf, acct_map
where to_char(bmf.trans_date, 'YYYY MM DD') = '1996 08 14' and
bmf.account_no = cmf.account_no and
acct_map.account_no = bmf.account_no
group by bmf.account_no, acct_map.external_id

Answer 1

This is an inner join, so I'm guessing that there are no records from Query 1 that share an account_no with acct_map.

You can quickly test this with the following query:

select count(*)
from
    acct_map
where
    account_no in (select bmf.account_no
            from bmf, cmf
            where to_char(bmf.trans_date, 'YYYY MM DD') = '1996 08 14' and
            bmf.account_no = cmf.account_no)

Answer 2

Eric is right, but let me add... shouldn't this be a view instead of moving data around?

Answer 3

Have you tried running your select query only first? I am guessing there is no account_no in table acct_map which is matching with account_no in bmf or cmf. Please check as your query seems to be fine so looks like a data issue.