hello all,
can anyone help me write an outer join b/n the two tables below. The joining condition has if's and or's.
table 1 has 2 million rows, table 2 is very small
| TABLE1 | | |
| CUSTOMER_ID | CITY | STATE |
| 1 | SKOKIE | IL |
| 2 | CHICAGO | IL |
| 3 | CARY | NC |
| . | ERIE | PA |
| . | PHILLY | PA |
| | CHARLOTE | NC |
| . | | |
| . | | |
| . | | |
| 2 MILLION | CITYXY | STATEX |
| TABLE2 | | |
| CITY | STATE | CONTACT |
| | IL | OJO |
| ERIE | | BRITT |
| | PA | MIKE |
| PITTSBURG | PA | HILTON |
| N043 | TAT | affi |
| | B | affi |
| | R | b0b |
| | Q | b0b |
| CHARLOTE | NC | b0b |
problem :: for all the data in table1, I need to find out the CONTACT from table 2 And the join condition would be as below
1. either both TABLE1.CITY=TABLE2.CITY AND TABLE1.STATE=TABLE2.STATE and get CONTACT
OR
2. TABLE1.CITY=TABLE2.CITY AND TABLE2.STATE IS NULL and get the value of CONTACT
OR
3. TABLE1.STATE=TABLE2.STATE AND TABLE2.CITY is null and get the value of CONTACT |
I need a query like this
SELECT A.CUSTOMER_ID, A.CITY, A.STATE, B.CONTACT
FROM TABLE1 A, TABLE2 B
WHERE (join condition fitting in the 3 condition mentioned above) |