CODEWITHSUNDEEP
sqloracle-database
Iqbal Hossain Ekra
Iqbal Hossain Ekra

Reputation: 1

How to join Two table In Orcale Both matched or not matched

I have two tables with some values. I want to join these tables. I need both which is matched and which is not matched. matched are in one row . not matched are in a single row. In my Table A want to join Based On CITY, I Have attached Table example on Picture. and what I exactly want that is in my picture as Out put

SELECT * FROM TEST1 A,  TEST2 B WHERE CUSTOMER_ID=CUSER_ID(+)
UNION 
SELECT * FROM TEST1 A,  TEST2 B WHERE CUSER_ID=CUSTOMER_ID(+)

output example

Upvotes: 0

Views: 54

Answers (2)

HereGoes
HereGoes

Reputation: 1320

To give you EXACTLY the output you specified... the following should give it to you. The columns you want are listed and on the second SELECT the cuser_id is used instead of customer_id.

SELECT a.customer_id, a.customer_name, a.city, b.user_name FROM TEST1 A,  TEST2 B WHERE CUSTOMER_ID=CUSER_ID(+)
UNION 
SELECT b.cuser_id customer_id, a.customer_name, a.city, b.user_name FROM TEST1 A,  TEST2 B WHERE CUSER_ID=CUSTOMER_ID(+)

Upvotes: 0

sticky bit
sticky bit

Reputation: 37477

You can do a full join. Use coalesce() on the city to get the non null city in case of non matching rows.

SELECT t1.customer_id,
       t1.customer_name,
       coalesce(t1.city, t2.city) city
       t2.user_id,
       t2.user_name
       FROM test1 t1
            FULL JOIN test2 t2
                      ON t2.city = t1.city;

And next time please don't post images of tables. Use DDL and DML or at least tabular text.

Upvotes: 2

Related Questions