Query on Joins

Database
Enthusiast

Query on Joins

Hi,

I have a patient(Hospital) details stored in my database.
The patient address details are stored in 2 tables. 1 for regular customers and other for 1 time customers.
Table1 consists of amount charged to the patient.

table1:
p.no address_no bill_amount tax_amount total_amount
1 001 100 10 110
2 003 200 20 220
3 007 300 30 330
4 005 400 40 440
5 009 500 50 550
6 008 600 60 660

table2:
p.no address_no patient_name patient_town patient_city
1 001 aaa abc mno
2 003 bbb abc kkk
3 007 ccc knm mno
4 005 ddd hmk jjj

table3:
p.no address_no patient_name patient_town patient_city
1 001 eee knm mno
6 008 fff abc kkk
5 009 ggg knm mno
4 005 hhh abc zzz

if the patient details(address_no) exists in both the tables(table2 and table3) then pick the details from table2. table3 is ignored if table2 has data.
we have to join on address_no.

I want the query for patient_town 'abc'

the output should be :

p.no. patient_name patient_town patient_city bill_amount tax_amount total_amount
1 aaa abc mno 100 10 110
2 bbb abc kkk 200 20 220
6 fff abc kkk 600 60 660
4 hhh abc zzz 400 40 440

Any help is greatly appreciated.

Thanks
Tags (1)
1 REPLY
Enthusiast

Re: Query on Joins

If I understand the question correctly, you would LEFT JOIN table1 to table2 and table3 on p_no, then use the COALESCE() function on the address columns so that table2's columns are chosen when they are not NULL.