Join and fetch columns based on priority

Question

CREATE TABLE orders
(
    product CHAR(3), 
    yr CHAR(4)
);

INSERT INTO orders VALUES ('a', '2019');
INSERT INTO orders VALUES ('m', '2019');

CREATE TABLE customers
(
    cus_name CHAR(20), 
    columnX CHAR(3), 
    columnY CHAR(3),
    price INT
);

INSERT INTO customers VALUES ('john', 'b', 'a', 100);
INSERT INTO customers VALUES ('brad', 'a', 'd', 200);
INSERT INTO customers VALUES ('chris', 'm', 'y', 200);
INSERT INTO customers VALUES ('Luis', 'r', 'm', 200);

I want to join orders table with customers table based on the column product in the orders table.

I want to fetch one price based on columns columnY or columnX for every product.

columnY should be given first priority. If product exists in columnY then fetch price from that row.

If it doesn't exist, then check columnX and fetch that price.

The below query with OR operation gives me both rows but I only want only the first row with cus_name as John

select *
from orders a
left join customers c on a.product = c.columnY 
                      or a.product = c.columnX

product	yr	cus_name	columnX	columnY	price
a	2019	john	b	a	100
a	2019	brad	a	d	200
m	2019	chris	m	y	200
m	2019	Luis	r	m	200

---

Expected output would be:

product	yr	cus_name	columnX	columnY	price
a	2019	john	b	a	100
m	2019	luis	r	m	200

Thanks in advance

Edit for clarity: every product will occur only once in columnX and columnY, i.e. ColumnY and ColumnX cannot have more than one Product 'a'

Edit 2 - Included multiple products in the orders table.

Answer 1

Using a CASE expression you can order by your defined priority and then use TOP 1 to take to first row.

This doesn't solve the issue of having 2 rows matching your priority though.

select top 1 *
from orders a
left join customers c on 
    a.product = c.columnY 
    or a.product = c.columnX
order by case when a.product = c.columnY then 1 else 0 end desc;

With your clarification which require multiple products you need a completely different solution. A common solution is to using the row_number() window function to find the first row per product e.g.

with cte as (
    select *
        -- Use the same ordering as before, but now partitioned by product.
        , row_number() over (partition by a.product order by case when a.product = c.columnY then 1 else 0 end desc) rn
    from #orders a
    left join #customers c on 
        a.product = c.columnY 
        or a.product = c.columnX
)
select product, yr, cus_name, columnX, columnY, price
from cte
-- Only pick the first result, ordered by our priority, per product
where rn = 1
order by product, yr, cus_name;

Which returns:

product	yr	cus_name	columnX	columnY	price
a	2019	john	b	a	100
m	2019	Luis	r	m	200

Answer 2

I would find all the target values for column "ColumnY" via "Intersect" and then use them as a filter.

Select orders.product, orders.yr, customers.cus_name, customers.columnX, customers.columnY, customers.price
From customers Inner Join orders On (customers.columnY=orders.product)
Where columnY In (Select columnY From customers 
                  Intersect
                  Select columnX From customers)

Answer 3

I changed @Dale answer based on comments and prepare the example (don't know is this helpful that if there is muliple year entries)

Declare @orders TABLE(product CHAR(3), yr CHAR(4));
INSERT INTO @orders VALUES ('a', '2019');
INSERT INTO @orders VALUES ('a', '2020');

Declare @customers TABLE(cus_name CHAR(20), columnX CHAR(3), columnY CHAR(3),price int );
INSERT INTO @customers VALUES ('john', 'b','a',100);
INSERT INTO @customers VALUES ('brad', 'a','d',200);

Select *
from @orders a
left join @customers c on 
    case when a.product = c.columnY then a.product else c.columnY end = a.product
    --a.product = c.columnY 
 --   or a.product = c.columnX
order by case when a.product = c.columnY then 1 else 0 end desc;

Result

if we remove the 2020 entry(same as current situation, then result will be)