Last day i came across a situation, where i needed to write a SELECTstatement which will use join with different tables but according to some conditions.
Lets take the below situation:
We have three tables as below :
Table : MEMBERS; Columns : MEMBERID, MEMBERNAME, MEMBERPHONE
Table : COMPANY; Columns : COMPANYID, FKMEMBERID, WORKPHONE
Table : RESIDENCE; Columns : RESIDENCEID, FKMEMBERID, HOMEPHONE
We need to write a select statement here, which will give us the result as :MEMBERID, MEMBERNAME, PHONENUMBER [which will come from COMPANY.WORKPHONE or RESIDENCE.HOMEPHONE ( If WORKPHONE isNULL) ].
Here, in select result PHONENUMBER should come from COMPANY.WORKPHONEand if COMPANY.WORKPHONE is NULL then PHONENUMBER should come fromRESIDENCE.HOMEPHONE.