CODEWITHSUNDEEP
sqljoin
Clinton Harrison
Clinton Harrison

Reputation: 9

SQL Join Query - Choose specific entry first, otherwise choose NULL

I'm sure this has been answered before, but I can't find it!

Table - Booking B

Customer    Product
Tim         Milk
Bob         Milk

Table - Product P

Customer    Product     Description
Tim         Milk         This is Tim's Milk
NULL        Milk         This is Anybody's Milk

I want to join the tables and get the following output (showing all columns in the example for simplicity):

B.Customer    B.Product   P.Customer     P.Product    P.Description
Tim           MILK        Tim            MILK         This is Tim's Milk
Bob           MILK        NULL           MILK         This is Anybody's Milk

So, the query should lookup to see if there is a specific customer related product first, and if so, use it, otherwise use the generic product...

Much appreciated!

Upvotes: 0

Views: 76

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

   SELECT B.Customer, B.Product,
          ISNULL(C.Customer,P.Customer) Customer,
          CASE WHEN C.Customer IS NULL THEN P.Description
               ELSE C.Description END Description
     FROM B
LEFT JOIN C ON C.Product = B.Product and C.Customer = B.Customer
LEFT JOIN P ON P.Product = B.Product and P.Customer IS NULL

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65314

I guess we need to join twice: First on cusotmer and product, then on product only

SELECT
  B.Customer,
  B.Product,
  IFNULL(Pfound.Customer,PnotFound.Customer) AS P_Customer,
  IFNULL(Pfound.Product,PnotFound.Product) AS P_Product,
  IFNULL(Pfound.Description,PnotFound.Description) AS P_Description
FROM B
  LEFT JOIN P AS Pfound
    ON B.Customer=Pfound.Customer
    AND B.Product=Pfound.Product
  LEFT JOIN P AS PnotFound
    ON B.Product=PnotFound.Product
    AND PnotFound.Customer IS NULL

Upvotes: 0

Related Questions