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

INSERT INTO orders 
VALUES ('a', '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);

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

Expected output would be:

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'

CodePudding user response：

Using a

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