Jointure SQL avec CASE et WHERE

Question

J'ai trois tableaux - un pour les frais de port, un pour les produits et un pour les exceptions aux frais de port pour des produits particuliers. Les frais de port sont facturés comme suit : Chaque produit a un prix d'expédition, mais ce prix peut être modifié par une exception. Si aucune exception n'existe pour un produit, le tarif par défaut est utilisé pour le tarif d'expédition sélectionné par l'utilisateur. texte alt http://mi6.nu/sqljoin.png J'essaie de joindre ces tables de sorte que si une exception existe, ce prix est sélectionné, sinon, le prix par défaut est sélectionné, mais j'ai des problèmes avec la jointure. J'ai besoin de faire une requête par ID de produit, et j'ai (la ligne 2 est pour le débogage)

SELECT r.ID AS ShippingRateID, r.Name, 
e.*, r.*
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID
WHERE e.ProductID = 48

Et j'ai besoin qu'on me le rende :

1   Uk and Northern Ireland 1
2   EU Eire...      10
3   US and Canada       2.16
4   Rest of world       2.44

Ainsi, si une exception existe, le prix de l'exception est utilisé, sinon c'est le prix par défaut qui est utilisé. J'envisage d'utiliser une instruction CASE, mais je dois d'abord renvoyer les données.

Answer 1

Pourquoi ne pas utiliser un coalesce

SELECT r.ID AS ShippingRateID, r.Name, coalesce(e.cost, r.defaultprice)
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID
WHERE e.ProductID = 48

Ainsi, si e.rate est nul, r.rate est utilisé.

Vous devriez également ajouter votre e.ProductId à la jointure afin de ne pas être obligé de sélectionner uniquement les produits avec des exceptions.

SELECT r.ID AS ShippingRateID, r.Name, coalesce(e.cost, r.defaultprice)
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID and e.ProductID = 48

Answer 2

La première chose que je remarque est que vous avez une condition prédictive sur la table e, qui se trouve du côté "extérieur" d'une jointure externe. Cela transformera immédiatement la jointure en une jointure interne, car les lignes qui auraient été produites ordinairement lorsqu'il y a un enregistrement du côté interne mais aucun enregistrement du côté externe, auraient toutes des zéros dans les colonnes de la table externe au moment où le prédicat de la clause where (WHERE e.ProductID = 48) est exécuté.

Vous devez placer ce prédicat dans les conditions de jointure.
comme ceci :

 SELECT r.ID AS ShippingRateID, r.Name, e.*, r.*
 FROM shipping r 
    LEFT JOIN  shippingexceptions e 
        ON r.ID = e.ShippingRateID
             And e.ProductID = 48

Ensuite, comme le recommande la réponse de Joels, pour une expression simple, bien qu'un cas puisse fonctionner, vous n'avez pas besoin d'une instruction de cas, Coalesce suffira :

 SELECT r.ID AS ShippingRateID, r.Name, 
     Coalesce(e.rate, defaultrate) as Rate,
     e.*, r.*
 FROM shipping r 
    LEFT JOIN  shippingexceptions e 
        ON r.ID = e.ShippingRateID
             And e.ProductID = 48

Answer 3

Si vous utilisez MSSQL, la fonction COALESCE pourrait vous aider. Elle renvoie la première valeur non nulle.

C'est le cas, select COALESCE(shippingexceptions.Rate, shipping.Rate )

Faire la même chose avec le cas quand :

Select Case when shippingexceptions.Rate is not null THEN shippingexceptions.Rate
ELSE shipping.Rate

J'espère que cela vous aidera.

Answer 4

SELECT  COALESCE(
        (
        SELECT  TOP 1 cost
        FROM    shippingexceptions se
        WHERE   se.ShippingRateID = r.id
                AND se.ProductID = 48
        ), DefaultPrice
        ) AS price
FROM    shipping r

ou en SQL Server 2005+ :

SELECT  COALESCE(cost, DefaultPrice) AS price
FROM    shipping r
OUTER APPLY
        (
        SELECT  TOP 1 cost
        FROM    shippingexceptions
        WHERE   ShippingRateID = r.id
                AND ProductID = 48
        ) se