Join query for multiple tables with condition-CodePudding

I'm using MSsql and I'm having a difficult time trying to get the results from a SELECT query. I have 3 tables.

First table Product
second table Seller
third table Customer

(data about customers - buyers and sellers).

select * from Product;

id(PK) | name_product
----------------------
1      | apple
2      | orange
3      | juice

select * from Seller;

id_seller(PK) | id_product | product_placement_date
---------------------------------------------------
45            | 3          | 2020-01-09
46            | 3          | 2020-01-05
58            | 2          | 2020-02-08
49            | 2          | 2020-01-04
43            | 1          | 2020-01-06

select * from Customer;

id_customer(PK) | name_customer
---------------------------
43          | Alice
45          | Sam
46          | Katy
49          | Soul
58          | Fab

I'm looking to select the name of the product and the first seller that placed that product ( given the first placement date ).

I've tried with this :

SELECT  C.name_product,
        P.mindate,
        P.name_customer
FROM Product AS C
    CROSS APPLY(SELECT MIN(S.product_placement_date) as mindate,
                            T.name_customer
                FROM Seller AS S
                JOIN Customer AS T ON T.id_customer = S.id_seller
                WHERE S.id_product = C.id) AS P

But I am not getting correct result. I want results as shown below:

name_product | product_placement_date | name_customer
-----------------------------------------------------
apple        | 2020-01-06             | Alice
orange       | 2020-01-04             | Soul
juice        | 2020-01-05             | Katy

Please advise

CodePudding user response：

Looks like you may have an issue with seller table. It APPEARS that the seller ID is the foreign key to the customer table. This would indicate that you would never allow the seller to sell any other item on any other date... unless the primary key for the table was the Seller ID, the item sold and the date thus pulling all 3 columns. I would expect the "Seller" table really be a "SellING" table and be more to a context of

SellingID (PK) | id_seller | id_product | product_placement_date
---------------------------------------------------
1              | 45            | 3          | 2020-01-09
2              | 46            | 3          | 2020-01-05
3              | 58            | 2          | 2020-02-08
4              | 49            | 2          | 2020-01-04
5              | 43            | 1          | 2020-01-06

Next consideration is what if two or more people were selling oranges and listed on the same day. On your original, you have no context of who listed theirs first... Or would you want ALL people who listed their product on the earliest date. Of which you could have both names shown. By having this "selling" table with a "sellingid" column as an auto-increment, you would then be able to KNOW who was first based on the earliest SELLINGID for a given product because somebody would have to commit their record first, even if on the same day. Then you might end up with something like

select
      p.name_product,
      S2.product_placement_date,
      c.name_customer
   from
      ( select id_product,
               min( sellingid ) as FirstListedID
           from
              selling
           group by
              id_product ) First
         join selling S2
            on First.FirstListedID = s2.sellingID
            join customer c
               on S2.id_seller = c.id_customer
            join product p
               on S2.id_product = p.id

Here, the pre-query of selling activity to the alias "First" represents a single list of all products with the first selling ID instance sold regardless of the date per the explanation why and using the auto-increment in the case of multiple people offering on the same date.

Once that is done, re-join to the original selling table on that first "ID". Then you can join out to the product and customer for the final details.

CodePudding user response：

    SELECT  P.name_product,
        S.product_placement_date,
        S.name_customer
FROM Product AS P
    CROSS APPLY(SELECT TOP 1 S.product_placement_date,
                            C.name_customer
                FROM Seller AS S
                    INNER JOIN Customer AS C ON C.id_customer = S.id_seller
                WHERE S.id_product = P.id
                ORDER BY S.product_placement_date) AS S