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.

Solution

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

Comments

[Anton]

This solution is incorrect, see the requirement and sample data. Your query returns 2 rows instead of 1.

[Andrew]

That won't join in the second case, c.ClientID is not null.

[Shushil Bohara]

I have edited the query and it's working fine as I have tested it before it was joining with alias C in last but it must be C1. Please Try

[Anton]

For the first data sample, it will return two rows as first join and second join match different rows

[Andrew]

That's working fine, although what goes in the select is an important part of the answer.

[Andrew]

No, this is working fine. They are two different joins, so that means more columns in the result, not more rows (like when using and OR in the join condition).

[Anton]

Added columns into SELECT

[Anton]

it is LEFT join, so: first join is successful for first row, second failed, result - first row is returned; first join is failed for second row, second join is successful, result - second row is also returned

[Anton]

So, it is more columns in the result AND more rows. It's like matrix, where the values are only on diagonal for data like in sample 1

[Andrew]

I ran the query against the data and it works fine. With the given data will only return one row. For first case, both joins will match, but we are only taking the first one as it's not null. In the second case, similar scenario, but as the first one is null, the second client table is used. The only improvement could be adding AND c.ClientId is null to the second join, but the output will be the same.

[Anton]

Andrew, both joins will match, but they match for DIFFERENT rows. You can't take a special row using ISNULL. ISNULL is for choosing correct column, not to choose a correct row. How come this answer be upvoted and my is still not....

[Anton]

Andrew, do you have a table create script and data insert script for case 1 to prove your statements?

[Shushil Bohara]

@Anton I think there might some data difference where you are applying this query and getting multiple records. I will work on that if you can provide full data set and will be helpful to me as well. Otherwise it's working fine with given data

[Anton]

@SBohara This is not my question, so I can consider only the data provided by Koo

[Andrew]

@Anton, I don't have the script, I created the tables manually. With this query you match two different rows of the client table, but each join only matches one row, so you get one row with the columns of tables M, C and C1, not two rows. Only if one of the joins matches more than 1 row is that you will get multiple result rows.

[Andrew]

c2.ID doesn't exist. And I don't think all that cases are needed, a simple ISNULL will do the trick, unless some row has null in the values to be returned, which at least here it's not the case.

[Koo SengSeng]

Andrew is right. By joining the client table twice with different condition and using the ISNULL to check if the first join column has data, if not use the second join column able to achieve what I need. Indeed, it returning more rows and the process took slightly longer but I guess that's necessary since my requirement need to cater for two set of matching. Thank you very much for the great answer and clarification

[Anton]

@Andrew, Ok, I got your point. For case 1, yes, it returns one row, but what if IDNumber is 1001 for the first row, so both row 1 and 2 may have same IDNumber? OP did not mention that IDNumber is always unique. My assumption was it was not unique as client table already has PK. So as per requirement, we still need to return ONE row. The solution in accepted answer returns 2 duplicated row, my solution still returns ONE as expected. My solution is more stable to such data anomalies. To be continued...

[Anton]

2Andrew. The problem #2. Look at 1st case. Imagine Token is NULL for the first row. The query returns incorrect result - it returns the value from the 2ns row, but it must return the value from the 1st row. My solutiion handles it. I strongly disagree with the decided accepted choice.

[Andrew]

Besides that, you have a typo in us null. ;)

[Andrew]

All that is fixed by simply adding AND c.client_id is null to the second join. As client_id is unique, if it joins with c, it won't join with c1, so if c.authorize_token happens to be null, the outcome will be null anyway.