OraFAQ Forum: SQL & PL/SQL » Joining multiple tables with case

Home » SQL & PL/SQL » SQL & PL/SQL » Joining multiple tables with case

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Joining multiple tables with case [message #682546]

Wed, 28 October 2020 05:38

aznabeel@yahoo.com
Messages: 1
Registered: October 2020

Junior Member

I have tables Table 1: CI_PER_PHONE & Table 2: CI_PER_NAME

I want join table 1 and table 2

I am looking to connect it with Table 2.

Both tables have "Per_ID" column which can be used to connect.

- CI_PER_NAME Columns Are:
Per_ID ,
ENTITY_NAME,
NAME_TYPE_FLG
- CI_PER_PHONE columns are mentioned in the below query.

----------
Table 1 Query : CI_PER_PHONE

SELECT "A1"."PER_ID" "PER_ID","A1"."'BUSNC'" "'BUSNC'","A1"."'HOME'" "'HOME'","A1"."'CELL'" "'CELL'","A1"."'MOBAC'" "'MOBAC'"
FROM (SELECT "A2"."PER_ID" "PER_ID",
MAX(CASE WHEN ("A2"."PHONE_TYPE_CD"='BUSNC') THEN "A2"."PHONE" END ) "'BUSNC'",
MAX(CASE WHEN ("A2"."PHONE_TYPE_CD"='HOME') THEN "A2"."PHONE" END ) "'HOME'",
MAX(CASE WHEN ("A2"."PHONE_TYPE_CD"='CELL') THEN "A2"."PHONE" END ) "'CELL'",
MAX(CASE WHEN ("A2"."PHONE_TYPE_CD"='MOBAC') THEN "A2"."PHONE" END ) "'MOBAC'" FROM
(SELECT "A3"."PER_ID" "PER_ID","A3"."PHONE_TYPE_CD" "PHONE_TYPE_CD","A3"."PHONE" "PHONE" FROM "CISADM"."CI_PER_PHONE" "A3") "A2" GROUP BY "A2"."PER_ID") "A1"
where ("A1"."'CELL'" is null ) AND ("A1"."'BUSNC'" is null) AND ("A1"."'MOBAC'" is null) AND ( "A1"."'HOME'" is null);
--------

Expected Output

Per_ID , ENTITY_NAME, NAME_TYPE_FLG , 'BUSNC' , 'HOME' , 'MOBAC' , 'CELL'

----------

Will appreciate if someone can guide......

Report message to a moderator

Re: Joining multiple tables with case [message #682547 is a reply to message #682546]

Wed, 28 October 2020 06:05

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Joining multiple tables with case [message #682609 is a reply to message #682547]

Mon, 02 November 2020 11:30

mskamene
Messages: 1
Registered: November 2020

Junior Member

SELECT
t1.per_id,
t1.entity_name,
t1.name_type_flg,
t2.BUSNC,
t2.HOME,
t2.CELL
from CI_PER_NAME t1
left join CI_PER_PHON t2
on t1.per_id=t2.per_id

Report message to a moderator

Re: Joining multiple tables with case [message #682611 is a reply to message #682609]

Mon, 02 November 2020 12:07

Michel Cadot
Messages: 68414
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The links I provided applies also to you, please read them.

There are no BUSNC, HOME... columns in the tables, so your "solution" is wrong.

Report message to a moderator

Re: Joining multiple tables with case [message #682612 is a reply to message #682546]

Mon, 02 November 2020 12:40

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

aznabeel@yahoo.com wrote on Wed, 28 October 2020 05:38

I have tables Table 1: CI_PER_PHONE & Table 2: CI_PER_NAME

I want join table 1 and table 2

I am looking to connect it with Table 2.

Just a side observation on coding style - when working with oracle you really should lose those double-quotes. Not only do they clutter up your code, making it more difficult to read, but in the oracle world, they mean your table/column names are case sensitive.

SQL> --
SQL> -- create case-INsensitive table
SQL> create table TestTable1 (id number);

Table created.

Elapsed: 00:00:00.09
SQL> -- see its entry in the data dictionary
SQL> select table_name
  2  from user_tables
  3  where lower(table_name) like 'test%';

TABLE_NAME
--------------------
TESTTABLE1

1 row selected.

Elapsed: 00:00:00.01
SQL> -- Reference it in various cases
SQL> select count(*) from TestTable1;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.01
SQL> select count(*) from testtable1;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.00
SQL> select count(*) from TeStTaBlE1;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.01
SQL> --
SQL> create table "TestTable2" (id number);

Table created.

Elapsed: 00:00:00.01
SQL> -- see its entry in the data dictionary
SQL> select table_name
  2  from user_tables
  3  where lower(table_name) like 'test%';

TABLE_NAME
--------------------
TESTTABLE1
TestTable2

2 rows selected.

Elapsed: 00:00:00.00
SQL> -- Reference it in various cases
SQL> select count(*) from TestTable2;
select count(*) from TestTable2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.01
SQL> select count(*) from testtable2;
select count(*) from testtable2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00
SQL> select count(*) from TeStTaBlE2;
select count(*) from TeStTaBlE2
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00
SQL> -- we can do it only when we refernce it exactly as it was created
SQL> select count(*) from "TestTable2";

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.00
SQL> -- clean up
SQL> drop table testtable1 purge;

Table dropped.

Elapsed: 00:00:00.02
SQL> drop table "TestTable2" purge;

Table dropped.

Elapsed: 00:00:00.01
SQL> --

Report message to a moderator

Previous Topic:	How to display only one row with max no. of count?
Next Topic:	ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 20 11:15:12 CDT 2023