How conditional join if first matching has no row, use second matching

Question

My client table has some special rows that work this way. There are multiple customer records that belongs to the same customer but with slightly different column value. The tables belong to them and I can't change anything in their table, so I have to work on a script to deal with this.

Now I need to compare customer data from main table and get the data from client table. The condition is as below:

1. If the main table Id exists in client table, get the only record matching with main table Id column

2. If the main table Id not exists in client table, use main table Idnumber to find and match Idnumber in client table.

Below is an example of what I'm trying to achieve:

Let's say this data exist in the main table and client table below:

In the scenario above, my script should always pick the client table PKId 1 only and ignore the row PKId 2 in client table by matching the main table Id column and client table ClientId column.

And for another scenario below:

Since the row PKId 1 has empty ClientId and there's no way to match the client Id 10 in client table, my script should use main table Idnumber to find and match record in the client table and would pick up the row PKId 2 by the Idnumber column.

I wanted to do a case in the join condition but not sure how I should construct it. I'm thinking about something like below (not actual SQL statement, just some idea):

Select 
    c.Id, c.Name, c.AuthorizeToken 
From 
    Client c 
Left Join 
    Main m on (If m.Id = c.ClientId has data return, get AuthorizeToken from that row only;
 else if m.Id = c.ClientId has no data return, use m.Idnumber = c.Idnumber     
 to find and get AuthorizeToken) 

Appreciate if can advice me on any alternative to achieve this.

Answer 1

You can also use LEFT JOIN instead

SELECT m.id, ISNULL(c.authorize_token, c1.authorize_token) authorize_token
FROM main m
LEFT JOIN client c ON m.id = c.client_id
LEFT JOIN client c1 ON m.idnumber = c1.idnumber