join all data for null

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
New Member

join all data for null

I have a lookup table which i want to join with customers table to find if they are eligible or not. 

 

 Rule Table

RuleIdProductidSegmentIdCost
110000?12
21002010
3?X15
4?Y10
5?Z55

 

so rule 3 to 5 says all products qualify. and rule 1 and 2 says all segments qualify with mentioned products. I have actualy created the code which works but i am looking if there is something more efficient. as i think i am missing something.

 

i want to achieve a result that will give me all possible combinations if null or an exact match if a value is available

 

SyntaxEditor Code Snippet

sel RuleId, P.Productid ,S.Segmentid,Cost   
FROM Rule R INNER JOIN Product p ON COALESCE(R.productid,P.productid) = P.productid
INNER JOIN Segment S ON COALESCE(R.segmentid,S.segmentid) = S.segmentid

 

1 REPLY
Teradata Employee

Re: join all data for null

Hi swedev,

 

You may have better performances using this query, but you have to test it :

    SELECT R.RuleId, P.Productid, S.Segmentid, R.Cost
      FROM Rule    as R
INNER JOIN Product as P ON P.productid = R.productid
CROSS JOIN Segment as S
     WHERE R.segmentid is null
     UNION ALL
    SELECT R.RuleId, P.Productid, S.Segmentid, R.Cost
      FROM Rule    as R
CROSS JOIN Product as p
INNER JOIN Segment as S ON S.Segmentid = R.segmentid
     WHERE R.productid is null;