Select same column from multiple tables with condition and LEFT JOIN-CodePudding

I have two tables with fiew similar columns :

One is for SellOrders

OrderID (PK)| id_seller| id_product| placement_date

And the other is for BuyOrders

OrderID (PK)| id_buyer| id_product| placement_date

With another table Customer concerning customers informations (Buyers and sellers)

id_customer(PK) | name_customer

And a Product table :

id(PK) | name_product

I want to write an SQL statement that will select the first placement_date when the a buy order or sell order was placed for the product and the correspending name_customer the name_product.

I wrote a query that select the same logic but for only the selling side and I am looking to select the data for both buy and sell side :

SELECT p.name_product, s.placement_date, c.name_customer
FROM Product p
OUTER APPLY (SELECT TOP 1 placement_date, id_seller
             FROM Selling
             WHERE id_product = p.id
             ORDER BY placement_date, OrderID ASC) s
LEFT JOIN Customer c 
ON c.id_customer = s.id_seller

From what I have seen UNION SELECT seems to be the way to do this. I added UNION to the OUTER APPLY:

OUTER APPLY (SELECT TOP 1 placement_date, id_seller
             FROM Selling
             UNION 
             SELECT TOP 1 placement_date, id_buyer
             WHERE id_product = p.id
             ORDER BY placement_date, OrderID ASC) s

But I get stuck at the LEFT JOIN with the table Customer. Any help ?

CodePudding user response：

If I've got it right you want first of union of buy/sell orders

SELECT p.name_product, s.placement_date, c.name_customer
FROM Product p
OUTER APPLY (
     SELECT TOP 1 placement_date, id_cust
     FROM ( 
        SELECT placement_date, OrderID, id_seller id_cust
        FROM SellOrders 
        WHERE id_product = p.id
        UNION 
        SELECT placement_date, OrderID, id_buyer
        FROM BuyOrders
        WHERE id_product = p.id
        ) t
      ORDER BY placement_date, OrderID ASC
   ) s
LEFT JOIN Customer c 
ON c.id_customer = s.id_cust