CODEWITHSUNDEEP
sqlmariadbone-to-many
BlackPage
BlackPage

Reputation: 107

One to Many request : retrieve only one value of many table depending on condition

I have two tables joined by a Produit_ID field :

Table Products
Produit_ID | Name
1          | Some product
2          | Some other product

Table Prices
Price_ID   | Produit_ID | Code_Prix | Validity_From | Validity_To
1          | 1          | 222       | 2024-01-01    | 2100-01-01
2          | 1          | 658       | 2024-01-01    | 2100-01-01
3          | 2          | 222       | 2024-01-01    | 2100-01-01

The conundrum is as follows : I need to get all fields from both tables but for the Prices table I need only the line containing 658 in Code_Prix if it exists else revert to the 222 Code Prix.

In other words, a request for both products should return the data from Product_ID = 1 and Price_ID = 2 for the first row and the data from Product_ID = 2 and Price_ID = 3 for the second row.

Performance is paramount here. It is the condition requiring me to get this data in a single request since the final query will fetch about 500 products/prices in a tables containing 15k/120k lines.

Can't seem to figure this one.

Any hint appreciated !

Upvotes: 0

Views: 63

Answers (3)

d r
d r

Reputation: 7846

You could Select Max(Code_Prix) per Produit_Id (filtered to just 658 and 222) and filter the result using it either as a correlated subquery in Where clause or defined as a cte and joined to your tables...

1. correlated subquery where filter

SELECT     p.Produit_Id, p.Name,
           c.Price_Id, c.Code_Prix, c.Validity_From, c.Validity_To
FROM       Products p
LEFT JOIN  Prices c ON( p.Produit_Id = c.Produit_Id ) 
WHERE      c.Code_Prix = ( Select Max(Code_Prix) 
                           From   Prices 
                           Where  Code_Prix In(658, 222) And Produit_Id = p.Produit_Id )

2. joined cte filter

WITH
  codes_prix AS
    ( Select   Produit_Id, Max(Code_Prix) as Code_Prix
      From     Prices
      Where    Code_Prix In(658, 222) 
      Group By Produit_Id
    )
SELECT     p.Produit_Id, p.Name,
           pcs.Price_Id, pcs.Code_Prix, pcs.Validity_From, pcs.Validity_To
FROM       Products p
INNER JOIN codes_prix cp ON( cp.Produit_id = p.Produit_Id )
INNER JOIN Prices pcs ON( pcs.Produit_Id = p.Produit_Id And pcs.Code_Prix = cp.Code_Prix )

Result is the same for both:

Produit_Id Name Price_Id Code_Prix Validity_From Validity_To
1 Some product 2 658 2024-01-01 2100-01-01
2 Some other product 3 222 2024-01-01 2100-01-01

fiddle

Upvotes: 0

Ergest Basha
Ergest Basha

Reputation: 9018

I would suggest using ROW_NUMBER().

The key part is the ORDER BY, if Code_Prix = 658 exists row_number will assign 1 , if Code_Prix = 658 does not exists and Code_Prix = 222 exists, the later will be assigned 1, if none of 222 and 658 exists (in this case you need to clarify which data would like to retrieve) random Code_Prix will be retrieved.

Use,

WITH cte AS (
      SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY Produit_ID ORDER BY CASE 
                                     WHEN Code_Prix = 658 THEN 1 
                                     WHEN Code_Prix = 222 THEN 2 ELSE 3 END ) AS rn
      FROM Prices p
) SELECT p.Produit_ID,
         p.Name,
         cte.Price_ID,
         cte.Code_Prix,
         cte.Validity_From,
         cte.Validity_To
 FROM Products p
 JOIN cte ON p.Produit_ID = cte.Produit_ID
 WHERE cte.rn = 1;

See example

Upvotes: 0

Barmar
Barmar

Reputation: 782158

Use two joins, one for each Code_Prix. Use LEFT JOIN for Code_Prix = 658 since it might not exist, then you can use COALESCE() to prefer it, falling back on the row for Code_Prix = 222.

SELECT products.*,
    COALESCE(p1.Price_ID, p2.Price_ID) AS Price_ID,
    COALESCE(p1.Code_Prix, p2.Code_Prix) AS Code_Prix,
    COALESCE(p1.Validity_From, p2.Valdity_From) AS Validity_From,
    COALESCE(p1.Validity_To, p2.Valdity_To) AS Validity_To
FROM products
LEFT JOIN prices AS p1 ON p1.Produit_ID = products.Produit_ID AND p1.Code_Prix = 658
INNER JOIN prices AS p2 ON p2.Produit_ID = products.Produit_ID AND p2.Code_Prix = 222

Upvotes: 0

Related Questions