Join two sql tables using multiple possible combinations

I need a way to join two tables that can handle the case where the default join method fails.

Lets say I have two tables, Customers with the fields you'd expect

And Orders

The obvious way to join these two tables is on Customer Id.

However, how do I structure a query so that in the case when there is no matching Customer Id, it switches to a second join method, i.e. on address, then if that fails, another fall back of First Name + Last Name (I know that's a bad join but roll with it for this example)

Thanks

I would probably do something like this:

with
	first_q as (
	SELECT
		c.CustomerId 	as customer_id
		,o.OrderId		as order_id
	FROM
				Customers	AS c
		join	Orders		AS o
			on	c.CustomerId  = o.CustomerId 
	)
	,second_q	as (
	SELECT
		c.CustomerId 	as customer_id
		,o.OrderId		as order_id
	FROM
				Customers	AS c
		join	Orders		AS o
			on	c.[Address] = o.[Delivery Address]
	WHERE
		o.OrderId NOT IN (SELECT order_id FROM first_q)
	)
	,third_q	as (
	SELECT
		c.CustomerId 	as customer_id
		,o.OrderId		as order_id
	FROM
				Customers	AS c
		join	Orders		AS o
			on	concat(c.[First Name], ' ', c.[Last Name]) = concat(o.[Customer FName], ' ', o.[Customer LName])
	WHERE
			o.OrderId NOT IN (SELECT order_id FROM first_q)
		and	o.OrderId NOT IN (SELECT order_id FROM second_q)
	)
SELECT
	customer_id
	,order_id
FROM
	first_q
UNION
SELECT
	customer_id
	,order_id
FROM
	second_q
UNION
SELECT
	customer_id
	,order_id
FROM
	third_q
;

Assuming that there might be multiple matches by the two latter joins, I would add some ranking functions to retrieve only the first result by OrderId.

In case you don't want to use a CTE where the default join method fails, you can handle the scenario with alternative join conditions, you can use left joins with coalesce statements to prioritize the join conditions. I would try

SELECT
    o.OrderId,
    o.CustomerId,
    COALESCE(c.FirstName, c2.FirstName) AS FirstName,
    COALESCE(c.LastName, c2.LastName) AS LastName,
    COALESCE(c.DOB, c2.DOB) AS DOB,
    COALESCE(c.Address, c2.Address) AS Address,
    o.[Delivery Address],
    o.[Other Order Info]
FROM
    Orders AS o
LEFT JOIN
    Customers AS c ON o.CustomerId = c.CustomerId
LEFT JOIN
    Customers AS c2 ON o.[Delivery Address] = c2.Address
        AND c.CustomerId IS NULL
LEFT JOIN
    Customers AS c3 ON o.[Customer FName] = c3.FirstName
        AND o.[Customer LName] = c3.LastName
        AND c.CustomerId IS NULL
        AND c2.Address IS NULL;

I performed left joins on the Customers table multiple times with different join conditions. The COALESCE function is used to select the first non-null value for each respective field.

The first left join attempts to join on the default condition o.CustomerId = c.CustomerId.

If the first join fails (c.CustomerId IS NULL), the second left join tries to match the address o.[Delivery Address] = c2.Address.

If both the first and second join fails (c.CustomerId IS NULL and c2.Address IS NULL), the third left join falls back to the join condition using the first name and last name o.[Customer FName] = c3.FirstName and o.[Customer LName] = c3.LastName.

In this way, the query will try different join conditions in sequence and return the first non-null values from the matching rows.