I have two tables as shown here:

and the other one is like:

Now I want to join these two tables so that I can get every customer id with ServiceID that have been purchased.

Any help would be appreciated.

CodePudding user response：

You have a heavily denormalized table structure, but it appears that this is not a join at all.

It seems to be purely a conditional unpivot of the second table, which you can do with CROSS APPLY

SELECT
  t2.UserId,
  v.*
FROM table2 t2
CROSS APPLY (
    SELECT 1
    WHERE Service1FirstOrderID IS NOT NULL
    UNION ALL
    SELECT 2
    WHERE Service2FirstOrderID IS NOT NULL
    UNION ALL
    SELECT 3
    WHERE Service3FirstOrderID IS NOT NULL
) v(Service);

db<>fiddle

CodePudding user response：

It's possible to join on an IN

SELECT
  so.userID
, CASE 
  WHEN o.OrderID = so.Service1FirstOrderID THEN 1
  WHEN o.OrderID = so.Service2FirstOrderID THEN 2
  WHEN o.OrderID = so.Service3FirstOrderID THEN 3
  END AS Service
FROM Orders o
INNER JOIN ServiceOrders so
  ON so.userID = o.customerID
 AND o.OrderID IN (so.Service1FirstOrderID, so.Service2FirstOrderID, so.Service3FirstOrderID)
ORDER BY o.customerID;

Demo on db<>fiddle here