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

my client table has special rows work way. there multiple customer records belongs same customer different column value. tables belong them , can't change in table, have work on script deal this.

now need compare customer data main table , data client table. condition below:

1. if main table id exists in client table, record matching main table id column

2. if main table id not exists in client table, use main table idnumber find , match idnumber in client table.

below example of i'm trying achieve:

let's data exist in main table , client table below:

in scenario above, script should pick client table pkid 1 , ignore row pkid 2 in client table matching main table id column , client table clientid column.

and scenario below:

since row pkid 1 has empty clientid , there's no way match client id 10 in client table, script should use main table idnumber find , match record in client table , pick row pkid 2 idnumber column.

i wanted case in join condition not sure how should construct it. i'm thinking below (not actual sql statement, idea):

select      c.id, c.name, c.authorizetoken       client c  left join      main m on (if m.id = c.clientid has data return, authorizetoken row only;  else if m.id = c.clientid has no data return, use m.idnumber = c.idnumber       find , authorizetoken)  

appreciate if can advice me on alternative achieve this.

you can use left join instead

select m.id, isnull(c.authorize_token, c1.authorize_token) authorize_token main m left join client c on m.id = c.client_id left join client c1 on m.idnumber = c1.idnumber