Forum Stats

  • 3,783,024 Users
  • 2,254,721 Discussions
  • 7,880,247 Comments

Discussions

outer joining 2 tables

User_58WZR
User_58WZR Member Posts: 72 Blue Ribbon

hi,

Using the following database version.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

I've got a 3 tables and only want the account name where the frequency type code is M. Here's my list of tables.

create table table1

(account_number varchar2(6));


insert into table1

(account_number)

values

('111111');


insert into table1

(account_number)

values

('222222');


insert into table1

(account_number)

values

('333333');


insert into table1

(account_number)

values

('444444');


create table table2

(frequency_type_id integer

,frequency_type_cd varchar2(1));


insert into table2

(frequency_type_id

,frequency_type_cd)

values

(1

,'D');


insert into table2

(frequency_type_id

,frequency_type_cd)

values

(2

,'M');


create table table3

(account_number varchar2(6)

,account_name  varchar2(10)

,frequency_type_id integer); --fk to table2


insert into table3

(account_number

,account_name

,frequency_type_id)

values

('111111'

,'test1'

,1);


insert into table3

(account_number

,account_name

,frequency_type_id)

values

('222222'

,'test2'

,1);


insert into table3

(account_number

,account_name

,frequency_type_id)

values

('333333'

,'test3'

,1);


insert into table3

(account_number

,account_name

,frequency_type_id)

values

('444444'

,'test4'

,1);


commit;


My query is as follows.

select table1.account_number

     ,table3.account_name

from  table1

left join  table3

on   table1.account_number = table3.account_number

left join table2

on   table3.frequency_type_id = table2.frequency_type_id 

and  table2.frequency_type_cd = 'M';


I'm looking to show the account_name only when finding records where frequency_type_cd is M. However, my query always returns the account_name regardless if I use M or D as the frequency_type_cd. How could the query be modified so that it only shows the account_name when finding records where frequency_type_cd = M? Thanks!!!

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 Red Diamond
    Accepted Answer

    Hi, @User_58WZR

    I was able to obtain the result I'm looking for using the Oracle syntax below. However, I'm looking to use the ANSI syntax to keep things consistent with our other queries.

    You can get exactly the same results like this:

    SELECT     table1.account_number
    ,	   table3.account_name
    ,	   table3.cash_balance
    FROM 	   table1
    CROSS JOIN table2
    LEFT JOIN  table3 ON  table3.account_number    = table1.account_number
       	  	  AND table3.frequency_type_id = table2.frequency_type_id
    WHERE	  table2.frequency_type_cd = 'M'
    ORDER BY  table1.account_number		-- or whatever you want
    ;
    

    Whether this really meets your requirements or not is another question.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond

    Well, all rows in table3 in your sample have frequency_type_id=1 which is D, so how do you expect M? Anyway, why do you need outer join?

    select  table1.account_number,
            table3.account_name
      from  table1,
            table2,
            table3
      where table1.account_number = table3.account_number
        and table3.frequency_type_id = table2.frequency_type_id 
        and table2.frequency_type_cd = 'M'
    /
    
    

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 Red Diamond

    Hi, @User_58WZR

    Thanks for posting the CREATE TABLE and INSERT statements and version. Don't forget to post the exact results you want from the given data. Are these the results you want?

    ACCOUNT_NUMBER  ACCOUNT_NAME
    --------------- -------------
    111111
    222222
    333333
    444444
    

    As Solomon said, it would be a better test if some of the rows had frequency_type_cd = 'M'.

    Here's one way to get the results above:

    SELECT   table1.account_number
    ,	 CASE
    	     WHEN table2.frequency_type_cd = 'M'
    	     THEN table3.account_name
    	  END AS account_name
    FROM 	  table1
    LEFT JOIN table3 ON  table1.account_number    = table3.account_number
    LEFT JOIN table2 ON  table3.frequency_type_id = table2.frequency_type_id 
       	 	 AND table2.frequency_type_cd = 'M'
    ORDER BY table1.account_number		-- or whatever you want
    ;
    

    Depending on your exact requirements, you may want some of the joins to be inner joins, and/or you may want some of what are join conditions above to be in a WHERE clause instead. What are your requirements? What does each row of the output represent? What columns (if any) are unique?

  • User_58WZR
    User_58WZR Member Posts: 72 Blue Ribbon

    hi @Solomon Yakobson ,

    Thanks for your response. In reality, table 3 could contain accounts for both monthly "M" and daily "D" data plus another attribute. I only want to show the account number when it finds a match for the monthly record (plus display the additional attribute like cash_balance).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 Red Diamond

    Hi, @User_58WZR

    I only want to show the account number when it finds a match for the monthly record (plus display the additional attribute like cash_balance).

    Okay, post the exact results you want from the given sample data, or from some new sample data (CREATE TABLE and INSERT statements) that has some monthly data, and a cash_balance column.

  • User_58WZR
    User_58WZR Member Posts: 72 Blue Ribbon

    ok Frank thanks. I've modified the create table and inserts to include the additional cash_balance column. However, I've purposely left the monthly data out of table 3. Here's the updated create table and insert script.

    drop table table1;
    drop table table2;
    drop table table3;
    
    
    create table table1
    (account_number varchar2(6));
    
    
    insert into table1
    (account_number)
    values
    ('111111');
    
    
    insert into table1
    (account_number)
    values
    ('222222');
    
    
    insert into table1
    (account_number)
    values
    ('333333');
    
    
    insert into table1
    (account_number)
    values
    ('444444');
    
    
    create table table2
    (frequency_type_id integer
    ,frequency_type_cd varchar2(1));
    
    
    insert into table2
    (frequency_type_id
    ,frequency_type_cd)
    values
    (1
    ,'D');
    
    
    insert into table2
    (frequency_type_id
    ,frequency_type_cd)
    values
    (2
    ,'M');
    
    
    create table table3
    (account_number varchar2(6)
    ,account_name   varchar2(10)
    ,cash_balance   number
    ,frequency_type_id integer); --fk to table2
    
    
    insert into table3
    (account_number
    ,account_name
    ,cash_balance
    ,frequency_type_id)
    values
    ('111111'
    ,'test1'
    ,700
    ,1);
    
    
    insert into table3
    (account_number
    ,account_name
    ,cash_balance
    ,frequency_type_id)
    values
    ('222222'
    ,'test2'
    ,800
    ,1);
    
    
    insert into table3
    (account_number
    ,account_name
    ,cash_balance
    ,frequency_type_id)
    values
    ('333333'
    ,'test3'
    ,900
    ,1);
    
    
    insert into table3
    (account_number
    ,account_name
    ,cash_balance
    ,frequency_type_id)
    values
    ('444444'
    ,'test4'
    ,1000
    ,1);
    
    
    commit;
    

    The result should appear as follows. Since there were no monthly records, the account_name and cash_balance should be blank. Hope this clarifies the requirement. Also, looking to use ANSI syntax.

    ACCOUNT_NUMBER     ACCOUNT_NAME     CASH_BALANCE
    111111             
    222222
    333333
    444444
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 Red Diamond

    Hi, @User_58WZR

    The result should appear as follows. ...

    ACCOUNT_NUMBER     ACCOUNT_NAME     CASH_BALANCE
    111111             
    222222
    333333
    444444
    

    Seriously, don't you think it would be better to test with some rows that did have values in the account_name and cash_balance columns?

    Earlier you had a column from table_3 that you only wanted displayed on monthly rows. Now you have a second cvolumn from table_3 that also is displayed only on monthly rows. Do the same thing for the second column that you did for the first column:

    SELECT   table1.account_number
    ,	 CASE
    	   WHEN table2.frequency_type_cd = 'M'
    	   THEN table3.account_name
    	 END AS account_name
    ,	 CASE
    	   WHEN table2.frequency_type_cd = 'M'
    	   THEN table3.cash_balance
    	 END AS cash_balance
    FROM 	 table1
    LEFT JOIN table3 ON  table1.account_number  = table3.account_number
    LEFT JOIN table2 ON  table3.frequency_type_id = table2.frequency_type_id 
       	 	 AND table2.frequency_type_cd = 'M'
    ORDER BY table1.account_number		-- or whatever you want
    ;
    


  • User_58WZR
    User_58WZR Member Posts: 72 Blue Ribbon

    hi @Frank Kulash, thanks for your suggestion, but this won't work. Problem is that table3 can contain the same account number for both daily and monthly. Let's just say the table gets wiped out and repopulated daily. But, on the last day of the month the table is supposed to contain all accounts listed twice, once for daily and once for monthly. The query I'm writing is supposed to only get the monthly data. If the monthly data doesn't exist for an account, then the account_name and balance should display as null. I was able to obtain the result I'm looking for using the Oracle syntax below. However, I'm looking to use the ANSI syntax to keep things consistent with our other queries.

    select table1.account_number
           ,table3.account_name
           ,table3.cash_balance
    from   table1
           ,table2
           ,table3
    where  table1.account_number = table3.account_number (+)
    and    table2.frequency_type_id = table3.frequency_type_id (+)
    and    table2.frequency_type_cd = 'M'
    

    I inserted an additional record to illustrate my point.

    insert into table3
    (account_number
    ,account_name
    ,cash_balance
    ,frequency_type_id)
    values
    ('444444'
    ,'test4'
    ,1000
    ,2); --monthly freq type cd
    

    The result shows as follows using the Oracle syntax.

    account_number    account_name    cash_balance
    444444	          test4	          1000
    222222		
    333333		
    111111		
    
    
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,407 Red Diamond
    Accepted Answer

    Hi, @User_58WZR

    I was able to obtain the result I'm looking for using the Oracle syntax below. However, I'm looking to use the ANSI syntax to keep things consistent with our other queries.

    You can get exactly the same results like this:

    SELECT     table1.account_number
    ,	   table3.account_name
    ,	   table3.cash_balance
    FROM 	   table1
    CROSS JOIN table2
    LEFT JOIN  table3 ON  table3.account_number    = table1.account_number
       	  	  AND table3.frequency_type_id = table2.frequency_type_id
    WHERE	  table2.frequency_type_cd = 'M'
    ORDER BY  table1.account_number		-- or whatever you want
    ;
    

    Whether this really meets your requirements or not is another question.

  • User_58WZR
    User_58WZR Member Posts: 72 Blue Ribbon

    You can get exactly the same results like this:

    ok @Frank Kulash , I think this is what I'm looking for. I'll put this in the application code and see what happens!