Home » SQL & PL/SQL » SQL & PL/SQL » Joining multiple tables with case
Joining multiple tables with case [message #682546] Wed, 28 October 2020 05:38 Go to next message
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......
Re: Joining multiple tables with case [message #682547 is a reply to message #682546] Wed, 28 October 2020 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
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.
Re: Joining multiple tables with case [message #682609 is a reply to message #682547] Mon, 02 November 2020 11:30 Go to previous messageGo to next message
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
Re: Joining multiple tables with case [message #682611 is a reply to message #682609] Mon, 02 November 2020 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67546
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.

Re: Joining multiple tables with case [message #682612 is a reply to message #682546] Mon, 02 November 2020 12:40 Go to previous message
EdStevens
Messages: 1282
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> --
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:
  


Current Time: Sun Nov 29 19:50:30 CST 2020