Conditional join on multiple columns or huge CASE?

• Vegeta7

  Mr or Mrs. 500

  Points: 572

  More actions

  June 29, 2018 at 10:56 am

  #414019

  Hi all,

  I'm trying to solve an exercise and can't seem to solve this without doing a huge case to cover all scenarios.

  I have these 2 Tables:
  

  My objective is to create a query to return the column in blue named "Covered?".

  Problem description:
  I have a table dbo.Products and I need to check for each product if at least the same types from the dbo.Lookup_Table exist in the dbo.Products Table.

  For example:
  - Product A needs to have Type1 = XOP and Type2 = GGS. Since the other columns are empty in the dbo.Lookup_Table, they can be whatever code in the dbo.Products Table. So the Covered column is set to True.
  - Product B in dbo.Products has 2 lines in dbo.lookup_table, which means that in the table dbo.Products if at least Type2 = WQW or Type3 = COL then the column Covered is set to True.
  - Product C has Type1, Type2 and Type4 correct but one is different from the lookup_Table, therefore it's false.
  - and so on..

  Here are the queries for creating the tables:
  CREATE TABLE dbo.Products (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Products
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','HIP',''),
('B','','WQW','',''),
('C','CCA','HJI','AAN','ABS'),
('D','','AER','FOF',''),
('E','ARD','','POU',''),
('F','AQW','SSC','POL','FRT')
;

  CREATE TABLE dbo.Lookup_Table (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Lookup_Table
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','',''),
('B','','WQW','',''),
('B','','','COL',''),
('C','CCA','HJI','JJI','ABS'),
('D','','','FOF',''),
('F','','','POL','')
;

  I have only thought about a scenario where I create a case to cover all the 4x3x2x1 possible cases, so a huge case with 24 WHENs.

  There must be another way to do this, so I was hoping to hear some suggestions from you guys 🙂

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 29, 2018 at 12:06 pm

  #1995850

  Vegeta7 - Friday, June 29, 2018 10:56 AM

  Hi all,

  I'm trying to solve an exercise and can't seem to solve this without doing a huge case to cover all scenarios.

  I have these 2 Tables:
  

  My objective is to create a query to return the column in blue named "Covered?".

  Problem description:
  I have a table dbo.Products and I need to check for each product if at least the same types from the dbo.Lookup_Table exist in the dbo.Products Table.

  For example:
  - Product A needs to have Type1 = XOP and Type2 = GGS. Since the other columns are empty in the dbo.Lookup_Table, they can be whatever code in the dbo.Products Table. So the Covered column is set to True.
  - Product B in dbo.Products has 2 lines in dbo.lookup_table, which means that in the table dbo.Products if at least Type2 = WQW or Type3 = COL then the column Covered is set to True.
  - Product C has Type1, Type2 and Type4 correct but one is different from the lookup_Table, therefore it's false.
  - and so on..

  Here are the queries for creating the tables:
  CREATE TABLE dbo.Products (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Products
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','HIP',''),
('B','','WQW','',''),
('C','CCA','HJI','AAN','ABS'),
('D','','AER','FOF',''),
('E','ARD','','POU',''),
('F','AQW','SSC','POL','FRT')
;

  CREATE TABLE dbo.Lookup_Table (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Lookup_Table
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','',''),
('B','','WQW','',''),
('B','','','COL',''),
('C','CCA','HJI','JJI','ABS'),
('D','','','FOF',''),
('F','','','POL','')
;

  I have only thought about a scenario where I create a case to cover all the 4x3x2x1 possible cases, so a huge case with 24 WHENs.

  There must be another way to do this, so I was hoping to hear some suggestions from you guys 🙂

  Vegeta7 - Friday, June 29, 2018 10:56 AM

  Hi all,

  I'm trying to solve an exercise and can't seem to solve this without doing a huge case to cover all scenarios.

  I have these 2 Tables:
  

  My objective is to create a query to return the column in blue named "Covered?".

  Problem description:
  I have a table dbo.Products and I need to check for each product if at least the same types from the dbo.Lookup_Table exist in the dbo.Products Table.

  For example:
  - Product A needs to have Type1 = XOP and Type2 = GGS. Since the other columns are empty in the dbo.Lookup_Table, they can be whatever code in the dbo.Products Table. So the Covered column is set to True.
  - Product B in dbo.Products has 2 lines in dbo.lookup_table, which means that in the table dbo.Products if at least Type2 = WQW or Type3 = COL then the column Covered is set to True.
  - Product C has Type1, Type2 and Type4 correct but one is different from the lookup_Table, therefore it's false.
  - and so on..

  Here are the queries for creating the tables:
  CREATE TABLE dbo.Products (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Products
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','HIP',''),
('B','','WQW','',''),
('C','CCA','HJI','AAN','ABS'),
('D','','AER','FOF',''),
('E','ARD','','POU',''),
('F','AQW','SSC','POL','FRT')
;

  CREATE TABLE dbo.Lookup_Table (
Product VARCHAR (50),
Type1 VARCHAR (50),
Type2 VARCHAR (50),
Type3 VARCHAR (50),
Type4 VARCHAR (50))
;

  INSERT INTO dbo.Lookup_Table
(Product,Type1,Type2,Type3,Type4)
VALUES
('A','XOP','GGS','',''),
('B','','WQW','',''),
('B','','','COL',''),
('C','CCA','HJI','JJI','ABS'),
('D','','','FOF',''),
('F','','','POL','')
;

  I have only thought about a scenario where I create a case to cover all the 4x3x2x1 possible cases, so a huge case with 24 WHENs.

  There must be another way to do this, so I was hoping to hear some suggestions from you guys 🙂

  I'm not sure if you need a join, but just to validate the rows with the lookup table, you can use an EXISTS.
  
SELECT *
FROM dbo.Products AS p
WHERE EXISTS(SELECT *
         FROM dbo.Lookup_Table AS lt
         WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
         AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
         AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
         AND (p.Type4 = lt.Type4 OR lt.Type4 = ''));


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 29, 2018 at 12:22 pm

  #1995853

  I came up with this:
  
SELECT
  [p].[Product]
  , [p].[Type1]
  , [p].[Type2]
  , [p].[Type3]
  , [p].[Type4]
  , [lt].[Product]
  , [lt].[Type1]
  , [lt].[Type2]
  , [lt].[Type3]
  , [lt].[Type4]
  , CASE WHEN (NULLIF([lt].[Type1],'') IS NULL OR [lt].[Type1] = [p].[Type1])
              AND (NULLIF([lt].[Type2],'') IS NULL OR [lt].[Type2] = [p].[Type2])
              AND (NULLIF([lt].[Type3],'') IS NULL OR [lt].[Type3] = [p].[Type3])
              AND (NULLIF([lt].[Type4],'') IS NULL OR [lt].[Type4] = [p].[Type4])
         THEN 'True'
         ELSE 'False'
    END AS [Covered]
FROM
  [dbo].[Products] AS [p]
  INNER JOIN [dbo].[Lookup_Table] AS [lt]
    ON [lt].[Product] = [p].[Product];
GO


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 29, 2018 at 12:24 pm

  #1995856

  Looks like Luis and I were at least thinking along the same lines, just a slightly different solution.

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  June 29, 2018 at 12:36 pm

  #1995862

  Here's another way:

  DROP TABLE IF EXISTS #Products;

  CREATE TABLE #Products
(
  Product VARCHAR(50) PRIMARY KEY CLUSTERED
, Type1 VARCHAR(50)
, Type2 VARCHAR(50)
, Type3 VARCHAR(50)
, Type4 VARCHAR(50)
);

  INSERT #Products
(
  Product
, Type1
, Type2
, Type3
, Type4
)
VALUES
(
  'A', 'XOP', 'GGS', 'HIP', ''
)
,(
  'B', '', 'WQW', '', ''
)
,(
  'C', 'CCA', 'HJI', 'AAN', 'ABS'
)
,(
  'D', '', 'AER', 'FOF', ''
)
,(
  'E', 'ARD', '', 'POU', ''
)
,(
  'F', 'AQW', 'SSC', 'POL', 'FRT'
);

  DROP TABLE IF EXISTS #ProductLookup;

  CREATE TABLE #ProductLookup
(
  Product VARCHAR(50)
, Type1 VARCHAR(50)
, Type2 VARCHAR(50)
, Type3 VARCHAR(50)
, Type4 VARCHAR(50)
);

  INSERT #ProductLookup
(
  Product
, Type1
, Type2
, Type3
, Type4
)
VALUES
(
  'A', 'XOP', 'GGS', '', ''
)
,(
  'B', '', 'WQW', '', ''
)
,(
  'B', '', '', 'COL', ''
)
,(
  'C', 'CCA', 'HJI', 'JJI', 'ABS'
)
,(
  'D', '', '', 'FOF', ''
)
,(
  'F', '', '', 'POL', ''
);

  SELECT *
FROM #Products p;

  SELECT *
FROM #ProductLookup pl;

  SELECT
  p.Product
, p.Type1
, p.Type2
, p.Type3
, p.Type4
, Covered = ISNULL(cov.Covered,'N')
FROM
  #Products p
OUTER APPLY
  (
   SELECT Covered = 'Y'
   FROM #ProductLookup pl
   WHERE
     p.Product = pl.Product
     AND EXISTS
   (
    SELECT
      p.Type1
    ,   p.Type2
    ,   p.Type3
    ,   p.Type4
    INTERSECT
    SELECT
      IIF(pl.Type1 = '', p.Type1, pl.Type1)
    ,IIF(pl.Type2 = '', p.Type2, pl.Type2)
    ,IIF(pl.Type3 = '', p.Type3, pl.Type3)
    ,IIF(pl.Type4 = '', p.Type4, pl.Type4)
   )
  )   cov;

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 29, 2018 at 12:43 pm

  #1995867

  Luis's and Phil's appear to be equals.  I have done a statistics io,time but the plans are nearly identical.

• Vegeta7

  Mr or Mrs. 500

  Points: 572

  More actions

  June 29, 2018 at 7:03 pm

  #1995905

  Luis Cazares - Friday, June 29, 2018 12:06 PM

  I'm not sure if you need a join, but just to validate the rows with the lookup table, you can use an EXISTS.
  
SELECT *
FROM dbo.Products AS p
WHERE EXISTS(SELECT *
         FROM dbo.Lookup_Table AS lt
         WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
         AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
         AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
         AND (p.Type4 = lt.Type4 OR lt.Type4 = ''));


  Great Luis Cazares! This will give me all the True values as you said. Thanks a ton 😀 I'll do a union to get all the false values. On the other hand, like Lynn Pettis said, his solution is similar but done in the case. But there's a slight situation with it, which I think will also happen with this one if I add the rows that are "False" 🙂 I'll show it below.

  _________________________________________________________________________________________________________________________

  Lynn Pettis - Friday, June 29, 2018 12:43 PM

  Luis's and Phil's appear to be equals.  I have done a statistics io,time but the plans are nearly identical.

  Thanks Lynn Pettis, indeed your solutions are very identical 🙂 But the thing is (below is the result of your query):

  

  As you can see, the product B is getting both True and False values. Is there a quick way to get around this? (without doing another case on top of this one saying that when a product is duplicated then select the one that is "true").

  _________________________________________________________________________________________________________________________

  Phil Parkin - Friday, June 29, 2018 12:36 PM

  Here's another way:

  SELECT
    p.Product
  , p.Type1
  , p.Type2
  , p.Type3
  , p.Type4
  , Covered = ISNULL(cov.Covered,'N')
  FROM
    #Products p
  OUTER APPLY
    (
     SELECT Covered = 'Y'
     FROM #ProductLookup pl
     WHERE
       p.Product = pl.Product
       AND EXISTS
     (
      SELECT
        p.Type1
      ,   p.Type2
      ,   p.Type3
      ,   p.Type4
      INTERSECT
      SELECT
        IIF(pl.Type1 = '', p.Type1, pl.Type1)
      ,IIF(pl.Type2 = '', p.Type2, pl.Type2)
      ,IIF(pl.Type3 = '', p.Type3, pl.Type3)
      ,IIF(pl.Type4 = '', p.Type4, pl.Type4)
     )
    )   cov;[/code]

  Thank you too Phil Parkin. Your solution gives the exact match I was looking for. Too bad I can't use OUTER APPLY on SQLPostgre which is where I need this. But I will analyze your query as I love to learn more about the APPLY operator on my SSMS Machine.

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  June 30, 2018 at 1:49 am

  #1995912

  Vegeta7 - Friday, June 29, 2018 7:03 PM

  Luis Cazares - Friday, June 29, 2018 12:06 PM

  I'm not sure if you need a join, but just to validate the rows with the lookup table, you can use an EXISTS.
  
SELECT *
FROM dbo.Products AS p
WHERE EXISTS(SELECT *
         FROM dbo.Lookup_Table AS lt
         WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
         AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
         AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
         AND (p.Type4 = lt.Type4 OR lt.Type4 = ''));


  Great Luis Cazares! This will give me all the True values as you said. Thanks a ton 😀 I'll do a union to get all the false values. On the other hand, like Lynn Pettis said, his solution is similar but done in the case. But there's a slight situation with it, which I think will also happen with this one if I add the rows that are "False" 🙂 I'll show it below.

  _________________________________________________________________________________________________________________________

  Lynn Pettis - Friday, June 29, 2018 12:43 PM

  Luis's and Phil's appear to be equals.  I have done a statistics io,time but the plans are nearly identical.

  Thanks Lynn Pettis, indeed your solutions are very identical 🙂 But the thing is (below is the result of your query):

  

  As you can see, the product B is getting both True and False values. Is there a quick way to get around this? (without doing another case on top of this one saying that when a product is duplicated then select the one that is "true").

  _________________________________________________________________________________________________________________________

  Phil Parkin - Friday, June 29, 2018 12:36 PM

  Here's another way:

  SELECT
    p.Product
  , p.Type1
  , p.Type2
  , p.Type3
  , p.Type4
  , Covered = ISNULL(cov.Covered,'N')
  FROM
    #Products p
  OUTER APPLY
    (
     SELECT Covered = 'Y'
     FROM #ProductLookup pl
     WHERE
       p.Product = pl.Product
       AND EXISTS
     (
      SELECT
        p.Type1
      ,   p.Type2
      ,   p.Type3
      ,   p.Type4
      INTERSECT
      SELECT
        IIF(pl.Type1 = '', p.Type1, pl.Type1)
      ,IIF(pl.Type2 = '', p.Type2, pl.Type2)
      ,IIF(pl.Type3 = '', p.Type3, pl.Type3)
      ,IIF(pl.Type4 = '', p.Type4, pl.Type4)
     )
    )   cov;[/code]

  Thank you too Phil Parkin. Your solution gives the exact match I was looking for. Too bad I can't use OUTER APPLY on SQLPostgre which is where I need this. But I will analyze your query as I love to learn more about the APPLY operator on my SSMS Machine.

  "Great Luis Cazares! This will give me all the True values as you said. Thanks a ton 😀 I'll do a union to get all the false values"

  We can also make use of a left join for getting the entire result set, rather than a union
  
select a.*,case when b.Product is null then 'False' else 'True' end as covered
  from dbo.Products a
left join ( --Luis Query here
           SELECT *
             FROM dbo.Products AS p
            WHERE EXISTS(SELECT *
                           FROM dbo.Lookup_Table AS lt
                          WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
                            AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
                            AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
                            AND (p.Type4 = lt.Type4 OR lt.Type4 = '')
                           )
            )b
         on a.Product=b.Product     


• Vegeta7

  Mr or Mrs. 500

  Points: 572

  More actions

  June 30, 2018 at 6:14 am

  #1995915

  george_at_sql - Saturday, June 30, 2018 1:49 AM

  "Great Luis Cazares! This will give me all the True values as you said. Thanks a ton 😀 I'll do a union to get all the false values"

  We can also make use of a left join for getting the entire result set, rather than a union
  
select a.*,case when b.Product is null then 'False' else 'True' end as covered
  from dbo.Products a
left join ( --Luis Query here
           SELECT *
             FROM dbo.Products AS p
            WHERE EXISTS(SELECT *
                           FROM dbo.Lookup_Table AS lt
                          WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
                            AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
                            AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
                            AND (p.Type4 = lt.Type4 OR lt.Type4 = '')
                           )
            )b
         on a.Product=b.Product     


  Yes, this completes it 😀 Thank you very much!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 30, 2018 at 10:35 am

  #1995918

  Vegeta7 - Friday, June 29, 2018 7:03 PM

  Luis Cazares - Friday, June 29, 2018 12:06 PM

  I'm not sure if you need a join, but just to validate the rows with the lookup table, you can use an EXISTS.
  
SELECT *
FROM dbo.Products AS p
WHERE EXISTS(SELECT *
         FROM dbo.Lookup_Table AS lt
         WHERE (p.Type1 = lt.Type1 OR lt.Type1 = '')
         AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
         AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
         AND (p.Type4 = lt.Type4 OR lt.Type4 = ''));


  Great Luis Cazares! This will give me all the True values as you said. Thanks a ton 😀 I'll do a union to get all the false values. On the other hand, like Lynn Pettis said, his solution is similar but done in the case. But there's a slight situation with it, which I think will also happen with this one if I add the rows that are "False" 🙂 I'll show it below.

  _________________________________________________________________________________________________________________________

  Lynn Pettis - Friday, June 29, 2018 12:43 PM

  Luis's and Phil's appear to be equals.  I have done a statistics io,time but the plans are nearly identical.

  Thanks Lynn Pettis, indeed your solutions are very identical 🙂 But the thing is (below is the result of your query):

  

  As you can see, the product B is getting both True and False values. Is there a quick way to get around this? (without doing another case on top of this one saying that when a product is duplicated then select the one that is "true").

  _________________________________________________________________________________________________________________________

  Phil Parkin - Friday, June 29, 2018 12:36 PM

  Here's another way:

  SELECT
    p.Product
  , p.Type1
  , p.Type2
  , p.Type3
  , p.Type4
  , Covered = ISNULL(cov.Covered,'N')
  FROM
    #Products p
  OUTER APPLY
    (
     SELECT Covered = 'Y'
     FROM #ProductLookup pl
     WHERE
       p.Product = pl.Product
       AND EXISTS
     (
      SELECT
        p.Type1
      ,   p.Type2
      ,   p.Type3
      ,   p.Type4
      INTERSECT
      SELECT
        IIF(pl.Type1 = '', p.Type1, pl.Type1)
      ,IIF(pl.Type2 = '', p.Type2, pl.Type2)
      ,IIF(pl.Type3 = '', p.Type3, pl.Type3)
      ,IIF(pl.Type4 = '', p.Type4, pl.Type4)
     )
    )   cov;[/code]

  Thank you too Phil Parkin. Your solution gives the exact match I was looking for. Too bad I can't use OUTER APPLY on SQLPostgre which is where I need this. But I will analyze your query as I love to learn more about the APPLY operator on my SSMS Machine.

  I have to disagree, here is the output from my query:
ProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------A                  XOP          GGS          HIP                       A                 XOP         GGS                                 TrueB                               WQW                                    B                             WQW                                 TrueB                               WQW                                    B                                         COL                     FalseC                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         FalseD                               AER          FOF                       D                                         FOF                     TrueF                  AQW          SSC          POL          FRT          F                                         POL                     TrueProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered
------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------
A                  XOP          GGS          HIP                       A                 XOP         GGS                                 True
B                               WQW                                    B                             WQW                                 True
B                               WQW                                    B                                         COL                     False
C                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         False
D                               AER          FOF                       D                                         FOF                     True
F                  AQW          SSC          POL          FRT          F                                         POL                     True


• Vegeta7

  Mr or Mrs. 500

  Points: 572

  More actions

  June 30, 2018 at 11:33 am

  #1995921

  Lynn Pettis - Saturday, June 30, 2018 10:35 AM

  I have to disagree, here is the output from my query:
ProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------A                  XOP          GGS          HIP                       A                 XOP         GGS                                 TrueB                               WQW                                    B                             WQW                                 TrueB                               WQW                                    B                                         COL                     FalseC                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         FalseD                               AER          FOF                       D                                         FOF                     TrueF                  AQW          SSC          POL          FRT          F                                         POL                     TrueProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered
------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------
A                  XOP          GGS          HIP                       A                 XOP         GGS                                 True
B                               WQW                                    B                             WQW                                 True
B                               WQW                                    B                                         COL                     False
C                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         False
D                               AER          FOF                       D                                         FOF                     True
F                  AQW          SSC          POL          FRT          F                                         POL                     True


  Yes, I just removed the columns from the lookup table as I don't need those 🙂 The B product is returning 2 lines with True and False, that's what I was referring to.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 30, 2018 at 2:32 pm

  #1995923

  Vegeta7 - Saturday, June 30, 2018 11:33 AM

  Lynn Pettis - Saturday, June 30, 2018 10:35 AM

  I have to disagree, here is the output from my query:
ProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------A                  XOP          GGS          HIP                       A                 XOP         GGS                                 TrueB                               WQW                                    B                             WQW                                 TrueB                               WQW                                    B                                         COL                     FalseC                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         FalseD                               AER          FOF                       D                                         FOF                     TrueF                  AQW          SSC          POL          FRT          F                                         POL                     TrueProductProductName ProductType1 ProductType2 ProductType3 ProductType4 LookupProductName LookupType1 LookupType2 LookupType3 LookupType4 Covered
------------------ ------------ ------------ ------------ ------------ ----------------- ----------- ----------- ----------- ----------- -------
A                  XOP          GGS          HIP                       A                 XOP         GGS                                 True
B                               WQW                                    B                             WQW                                 True
B                               WQW                                    B                                         COL                     False
C                  CCA          HJI          AAN          ABS          C                 CCA         HJI         JJI         ABS         False
D                               AER          FOF                       D                                         FOF                     True
F                  AQW          SSC          POL          FRT          F                                         POL                     True


  Yes, I just removed the columns from the lookup table as I don't need those 🙂 The B product is returning 2 lines with True and False, that's what I was referring to.

  That is because you have two lookups for the Product B.

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  June 30, 2018 at 9:33 pm

  #1995926

  Vegeta7 - Friday, June 29, 2018 7:03 PM

  Too bad I can't use OUTER APPLY on SQLPostgre which is where I need this.

  This is patently an SQL Server 2017 forum.  It would be extremely helpful and save you and some people some time if you'd let people know that you're using some other RDBMS in the future.

  Borrowing heavily on what the others have done, the use of a LEFT (outer) JOIN will do the trick here without correlation, derived tables, or other fanfare.

  
     SELECT  p.*
            ,IsCovered = CASE WHEN lt.Product > '' THEN 'TRUE' ELSE 'FALSE' END
       FROM dbo.Products     AS p
  LEFT JOIN dbo.Lookup_Table AS lt
         ON p.Product = lt.Product
        AND (p.Type1 = lt.Type1 OR lt.Type1 = '')
        AND (p.Type2 = lt.Type2 OR lt.Type2 = '')
        AND (p.Type3 = lt.Type3 OR lt.Type3 = '')
        AND (p.Type4 = lt.Type4 OR lt.Type4 = '')
;


  Here are the results...
  
Product Type1 Type2 Type3 Type4 IsCovered
------- ----- ----- ----- ----- ---------
A       XOP   GGS   HIP         TRUE
B             WQW               TRUE
C       CCA   HJI   AAN   ABS   FALSE
D             AER   FOF         TRUE
E       ARD         POU         FALSE
F       AQW   SSC   POL   FRT   TRUE


  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Vegeta7

  Mr or Mrs. 500

  Points: 572

  More actions

  June 30, 2018 at 10:04 pm

  #1995927

  Jeff Moden - Saturday, June 30, 2018 9:33 PM

  Vegeta7 - Friday, June 29, 2018 7:03 PM

  Too bad I can't use OUTER APPLY on SQLPostgre which is where I need this.

  This is patently an SQL Server 2017 forum.  It would be extremely helpful and save you and some people some time if you'd let people know that you're using some other RDBMS in the future.
  

  You're absolutely right. It's the first time I'm doing this in SQLpostgre and totally forgot to mention it. Thank you very much for calling me out on this. I'll take that into consideration in future posts.

  Regarding your code, it's perfect and simple. Thanks a ton.

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  July 1, 2018 at 2:11 am

  #1995937

  Just for fun, here is a completely different kind of solution
  😎

  Using Phil's data sample
  

  ;WITH PROD_COMPARE AS
(
  SELECT
   PR.Product
   ,SUM(PX.CODE)      AS PXS
   ,ISNULL(SUM(PX.LCODE),100) AS PLS
  FROM  #Products PR
  LEFT OUTER JOIN #ProductLookup PL
  ON    PR.Product = PL.Product
  CROSS APPLY
  (
   SELECT 1, CASE WHEN PR.Type1 = PL.Type1 THEN 1 ELSE 0 END, SIGN(LEN(PL.Type1)) UNION ALL
   SELECT 2, CASE WHEN PR.Type2 = PL.Type2 THEN 1 ELSE 0 END, SIGN(LEN(PL.Type2)) UNION ALL
   SELECT 3, CASE WHEN PR.Type3 = PL.Type3 THEN 1 ELSE 0 END, SIGN(LEN(PL.Type3)) UNION ALL
   SELECT 4, CASE WHEN PR.Type4 = PL.Type4 THEN 1 ELSE 0 END, SIGN(LEN(PL.Type4))
  ) PX(COL,CODE,LCODE)
  GROUP BY PR.Product
)
SELECT
  PC.Product
 ,CASE
   WHEN PC.PXS >= PLS THEN 'Y'
   ELSE       'N'
  END AS IS_COVERED
FROM  PROD_COMPARE  PC;

  Output
  
Product IS_COVERED
-------- ----------
A   Y
B   Y
C   N
D   Y
E   N
F   Y