How to conditionally join tables-CodePudding

I have a table - Sales

Id	TypeNr	TypeId	Price	Quantity
1	0	10	4.32	120
2	1	20	2.38	0

Desired output:

Id	TypeNr	TypeId	Price	Quantity	Description
1	0	10	4.32	120	Blue Shirt
2	1	20	2.38	0	20% Discount

If TypeNr is 0 then I need to make a join to the Products table and pull the name associated with the TypeId.

If TypeNr is 1 then I need to make a join to the Discounts table and pull the discount associated with tye TypeId.

-- I have tried several methods with IFs / CASE/ELSE but my knowledge of SQL is very limited hence could not get the desired output.

CodePudding user response：

This looks like an excellent use case for UNION ALL.

Try this:

SELECT Id,
       TypeNr,
       s.TypeId,    
       Price,
       Quantity,
       p.TypeName as Description
FROM   Sales s
   INNER JOIN
       Products p
         ON s.TypeId = p.TypeId
WHERE  s.TypeNr = 0
UNION ALL
SELECT Id,
       TypeNr,
       s.TypeId,    
       Price,
       Quantity,
       d.Discount as Description
FROM   Sales s
   INNER JOIN
       Discounts d
         ON s.TypeId = d.TypeId
WHERE  s.TypeNr = 1

What this does:

UNION ALL merges two query result sets (with the same number and type of columns). The difference between a UNION and a UNION ALL is that a plain UNION will perform an implicit DISTINCT on the results, whereas a UNION ALL won't. As a result, UNION ALL is much faster to return results.

Since s.TypeNr can't be both 0 and 1, the two queries won't return any of the same records, so there's no need for a DISTINCT. Therefore, UNION ALL can be used.

(I am making some assumptions about field names and types for the sake of this example: adjust as needed!)

(Also, I chose inner joins for simplicity, but if there's any chance that a product description or discount might be missing, use LEFT OUTER JOINS.)

CodePudding user response：

SELECT Id, TypeNr, TypeId, Price, Quantity
CASE
    WHEN TypeNr = 1 THEN SELECT Description FROM Products AS p WHERE p.id = TypeNr
    WHEN TypeNr = 0 THEN SELECT Disc FROM Discounts AS d WHERE d.id = TypeNr
END AS Description;
FROM Sales;

CodePudding user response：

Using Subquery:

select
    Id,
    TypeNr,
    TypeId,
    Price,
    Quantity,
    case
        when
            TypeNr = 0 
        then
(
            select
                Description 
            from
                Products 
            where
                pTypeId = TypeId) 
            when
                TypeNr = 1 
            then
(
                select
                    Description 
                from
                    Discounts 
                where
                    pTypeId = TypeId) 
    end
    as Description 
                from
                    sales;

CodePudding user response：

You cannot join conditionally. Instead, join both tables and use a CASE to select the desired data.

SELECT
    s.Id, s.TypeNr, s.TypeId, s.Price, s.Quantity,
    CASE
      WHEN s.TypeNr = 0 THEN p.Name
      WHEN s.TypeNr = 1 THEN d.Discount
    END AS Description
FROM
    Sales s
    LEFT JOIN Products p ON s.ProductId = p.ProductId
    LEFT JOIN Discounts d ON s.DiscountId = d.DiscountId

You will have to adapt the JOIN ON clauses, as I don't know how you are joining the tables exactly.

I also assume that d.Discount is a text. Otherwise you will have to construct it. Something like this

CAST(d.Discount AS varchar(8))   '% Discount'

CodePudding user response：

You can make the joins conditional by adding additional logic to the on clauses. Then pick up the correct value, if any, with coalesce:

select S.Id, S.TypeNr, S.TypeId, S.Price, S.Quantity,
  Coalesce( P.Name, D.Discount ) as Description
  from Sales as S left outer join
    Products as P on S.TypeNr = 0 and S.TypeId = P.TypeId left outer join
    Discounts as D on S.TypeNr = 1 and S.TypeId = D.TypeId;

With the on conditions being mutually exclusive you are guaranteed to get at least one null result between P.Name and D.Discount and coalesce will pick up the other value. Note that both values could be null and, as mentioned by Olivier Jacot-Descombes, you may have some data type cleanup to do with the Discount.

Food for thought: How often do you use an unconditional, i.e. cross, join?