Post reply

Conditional join on multiple columns or huge CASE?

  • 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 🙂

  • 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

  • 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

  • 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.

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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.

  • 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         

  • 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!

  • 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):

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAYMAAAC+CAIAAABYlfSCAAAU1klEQVR4nO2dzarjOBqGfVHdBcm9NEUv4gsZKOhVyFU0NFOzCuQaatHQ2wO5gdoMVM+2A5mFE1mS9W990SvnfTgUdZKc5Iktv5HkWN9wfz++EkLAGFrHAiGE3Ock+vd/zvzhD3/40+THSKJba77//U9rhRJgtWHFFLCGFJPj+9//RPpErQ173cqw2rBiClhDisnBJJICVhtWTAFrSDE5mERSwGrDiilgDSkmB5NIClhtWDEFrCHF5GASSQGrDSumgDWkmBydJtH5MNgc1pieDyufYMlS++O4W1gPwzDsjh9VXxlTTH+RyKbmplsv9kA/UF7slEnHSWQ05/Nh1ZZ+SRLNfBx37dpFC7GP427ewMYvLrjpcnGKfRx3+kFxPqz9vBZlK0m0siEwiSaExOzPicjm5qbLJe0YWflxLcumkuhwvj1bxPGgRgHmSE7bE/Mdh0PbJLJ7Cc8mcz4Mw+E4DwCqKAqL6Zvb4xs7aN9101UVC6e946DwCd88+3RxoK1+C1tIIq0jOo3cPSOBeesanw8SHde8z0/9lvn/Z2M3V/pIkxQz94v7YLAGDS82XEVHYqG09x0UfmHXPjUPtNV0nESeKUJzF7giS30ymfnfdnQ236TdaWnVGQAIirm29rLrWjBjXc1wHR2JhZ7Xd1A4hb37tPJwtOMkSun6L7bW4wb79vZJpG7T70M4nDLEnCeS5qeb7k54A2+46eqL+Zu076BwCvv3KZPodktPon76RJPE7njWuxGOVrveUlAssBnNQUkzw3V0Jeafsfb2iVzC3n3KJLrdMpIoaZ4o5yhZpe12VDzGnKZu+slvADFzwk3dkTml8Jabrr6Y9yy+96CIC2t/yyS63TKS6OY/dzb3O6czAI/n05/a9/9Sba+jrmp31Q6HpfsaSVExqzuvz21aVD6L3/+mExHzfbPRe0LZefrGsU+Xb3jlgdNpEnVAibbdVRcYNAKLKWANNyUGBpNIinzt5acqSKt9kZgC1nBDYnAwiaTI0556y3bvHqDVvlBMAWu4ETFImERSwGrDiilgDSkmRzyJvv/9D3/4wx/+SP9EkugHAMMwdPovISSFZQw5kqhtt+3Hjx/f//u/7n58G7c5sGIKWEOKCdFNEg3D0DxZcn+GAbSAJX6rhTWkmBDdJFHzWGGf6JXAGlJMiG6SiH2iiuC3WlhDignRTRI1jxX2iV4JrCHFhOgmicJ9om9fPg3D8Msf7dMnpU90GYdhGMZL4Kbraa9dFLQ/Xe2HGhhPtWKvJ7x60gMMxVy5iOFlXD6luu162j9sKmylTLHG+MTMXRXcq01Zn0Sy37VVSRQ85v/618/DMAzD56/N0yexT3Q97bVD4zIaB4r16/S7ajmLQ/Ey5rar0OE0tVztBa6nvfH8kQdYDzffaAXDjCRauZUyxRoTF5s3DiIrkki7lvcFSRTqE/3520/Dr79P/7ZOn5Q+0f1+1w9R8xhxH7raY5aHYnYT87fa6Kvn6xUdAPWTqNKByCQSYgt9om9fPg2fv049o5++/NU8gFL6RPf786Aex2WPw9GHiB1jWd0Or1i0scZbc2EnyEIoidYP0LaVRNNNp+fUgLnRjN/00W6VYa5NN0nk7xN9/eU5Q/Tty6fh59++tQ6gtD7R/X5fjsPcB5p5h/0Ie/SUgLfVel89+QF3a2aisNWGk8hFJIkKtlKmWGOKk8iKG1cSBRKqGt0kkfeA/+PXQQ3K/vztJ6R569SJYb15pCXRysnH5CRafBJGH+B6fwUJIDBjXWdgsr0k0m7yJI4r1atHUTdJ5OsT/f558ekIM28d7hM9hzEp00ThcUc22aMzx6HueYD7OSue3SsendXhDZPIefat+oRTN0nkOdrnoZmji9T6J3qK6rHXjSwqmLHOJnfGWnvNyANcd5cIM4lyeWGfSIRuksjZJ/r25dMid77+MqDMW/v7RM7z9L6T+rGz+PlEI9L1Babna0YeYMq6psLWGTKJ3NRIIv1X/Rtu5j6sc07Cppsk8nWIlqHz++cBpFvk2biOLzYubox8s1EwiZavPowXe2Yg/IDoFyPXGDKJ3NRIIn3XjRdjE9Y4DRGkmyTyzRMh/4TniRoCezgpYA0pJkQ3V3s0j5V6faL2wIopYA0pJkQ3ScQ+UUXwWy2sIcWE6CaJmscK+0SvBNaQYkJ0k0TsE1UEv9XCGlJMiNQkarviP/tEdYEVU8AaUkyI1CRqzgBQpaPsX0JICklJ1Hx0VjeAXwOsNqyYAtaQYkIwiQSB1YYVU8AaUkwIJpEgsNqwYgpYQ4oJwSQSBFYbVkwBa0gxIZhEgsBqw4opYA0pJkTvSbRYEEt89YIMYBuHR2xxkbV9Xb31CN+VrrnPk27Ynu7E3qG2x8dxp96X7DWw4SSyy0zAhFF3rXa5QM1+71nAZlHhw0ib9OfJNWxNx2IbXVH/47ib88f4pWUSYW3t/lqtsfmmlT4uo2NppNhybqnPk2/Ymo7FoI6NBaVJdD4Mw+74YfwuF0VZfSKcjd1hq9U24GPJIW3lIXWnbzPPt6c9T4lhYzoW23ptj9vtNnWK9GB6aRJxniiXgJjR7Vn+J7IMmaMcROh5igzb0rHY1mt7PHJIMogyR2cwadRlq32OoubR1DNC5lsSkijpecoMm9Kx2NZre0zz1iAz1s5bmtFnq52CwprWmW6JDa+M2xOep9CwJR2Lbbm2x5RCkr0hJpEEQbHraT+M42i10P042itVm232kTHmufzY85QZtqRjsc3W9jgf5E/fZycRR2cphMUen4HLAovLNfXtrxa5VmcPP0+RYUM6FttmbY9XjMlSkwh2yrrXVrtsau5azma3fTzZaZT6PPmG7ehYbJO1PfQvNb7g242wuz8MrLac2PVyqTJ/8IabbiWwYon0frUHNLDasGIKWEOKCcEkEgRWG1ZMAWtIMSGYRILAasOKKWANKSYEk0gQWG1YMQWsIcWESE2i5rU9XrVBagKrDSumgDWkmBCpSUQIIaIkJRFHZwXAasOKKWANKSYEk0gQWG1YMQWsIcWEYBIJAqsNK6aANaSYEEwiQWC1YcUUsIYUE4JJJAisNqyYAtaQYkL0nkRcs7EEjxhre8TpT8y1vFBo0zdagXlNEmmXwQovURROItb2yMUrxtoeMfoTy02W3pLIKOchu54+a3vUJq3VsraHg/7ENp5E5hL6wgvqs7ZHZfxirO0RoT+xwNY25jaej7F23XLwLVPno8o8kXQQcZ6oMgEx1vYI05/Ycp5IG1+bQ2jzQ0Tfy759X29h2bVJNC0hCzNPhDVR1F+rvbO2R4T+xELBb32Qmx8U9nmG6/XqPuCqHG91zp3h1IDlivopBMVY2yNEf2KhnaXdYXRi9ZGaMTyTq/NR6yw+SA1Y5y3N6K/V3u+s7RGmPzFPEtk3O5Pobty9P13lDq/SJLKTByWJODpLISzG2h4B+hPz9YmMzw1txzwfb/6dUXJIos5HcZ/IjB7h0/gZM9YwMXTvsdVOsLaHn/7E/PNE+v4bR0efSD+6rGJE1Y+3NaOzx2z1MAztasBCA6stJ8baHq2AFUuk96s9oIHVhhVTwBpSTAgmkSCw2rBiClhDignBJBIEVhtWTAFrSDEhmESCwGrDiilgDSkmRGoSsbZHAbDasGIKWEOKCZGaRIQQIkpSEnF0VgCsNqyYAtaQYkIwiQSB1YYVU8AaUkwIJpEgsNqwYgpYQ4oJwSQSBFYbVkwBa0gxIZhEgsBqw4opYA0pJsQmkshzoV5zyrWD72ixmH1FMdeiD86FICKLSKwlelwFNoLrLvMK8vm27K1YJBZZWdS4u3S/esXeoLbHA+ll0sJJ5FjvCeZy/DLt2Dt6tqwV77L/JApsBOddj4N9cVv1JPK+uqcCjbW3y1fZCCbRhlfUN3OoXRK5V0oD6RgVacfe0bRc57pFO7tPosBGcN91GYdhvzePcokk8r+6pwLNQqJ0e755Ep0Pw+54FK4xFEgioAUaHZRox97Rs5Gvai+9J1FgI3jueugab0UgicKvrj/KWJ+1RhMuSaKt1Pb4OO6Gw1m+2pk/ibCKCi0o0I69I7P0Yel7DyeRC6gkCmwE313GkoOxugDVxYLzRJXWHcuYJ9pYbY9nDjGJvNRPIrtxFLaCvvtEgY3gvcssOuIbB0uJpa1xrCKjaHMWjc42UNvjfJgrC7VLoncbnTl6LHVnN3tIosBG8N+1GJalFhmpIpZT96H0AyY7iTZR2+MxTW2BsKL+pmesF3eVBnFZErknWV6dRIGNkH6XawL7lWLPW1yxU7hTc5NoK7U9dBr2id7pLL6v1RbEQGGfyBrirJ+vyjcMbITg9nHGRElTKRALjs7sIVBxA87uExltZ9ocRhL1UtsDJYmeW6lqT7ESVb/Z6A6dsoZbPDrTvJZzmTXxGAY2gnvnP7eP431V3XThvbMct3mnrOufhXiD2h4wSYQKrDasmALWkGJCbOJqD1RgtWHFFLCGFBOCSSQIrDasmALWkGJCMIkEgdWGFVPAGlJMCCaRILDasGIKWEOKCZGaRKztUQCsNqyYAtaQYkKkJhEhhIiSlEQcnRUAqw0rpoA1pJgQTCJBYLVhxRSwhhQTgkkkCKw2rJgC1pBiQjCJBIHVhhVTwBpSTAgmkSCw2rBiClhDignRexKFry1sDGzjiIolFaiYt7brrnVXxXoNI5Uq/FeT5pa4yBVrjU/MuaLQ6v1Tn/IkOh+styZ4EWw4iZarPYCkUXet9klKgYp78K61q0UEkyi4AqG1CpFreeY1dLtP0Vc4XZVEatVGaTKSCGmD99pq0wpU3MN3rVtSKz+J3Nk3r9bBJMI5MFwUJ9G8jDVaEuFs8U5bbVqBCh2AJPJ1wtTjmUSeSij70+m5TFpgtXyReh46q5Jot9u9YGiWnUQwq1v32WqTC1TMbfLFozOXg3efP+/w/WEtsdYUJ5FrSWDrN6l6HjrFSXQ+DOaa+iDzRN4bG9Blq00uUGH9Td0J0ew+UVISsU/k7BN5ur2uVYPVX1U/vmqdO5Ndt5Gjs7oExHIKVBh/ZE8pCZ474+jMgVwSydXzSPHPTaKP405wApsz1nXJPBcZGf/kVdFZacgZaw8v7BOJUJpEdh8IpU/k+iJMM7prtXnlMczH1Kk6EjPkWXwfNZJI/1Wr9iFWz0Nn1TyRyh5j0ujlSSTba1xBb602t0CFaqq+MXJ5ey1Kors9krC/2cgkCieRvv3Gi7FXRep56KwZnWnfbRT+ZhHs7g8Dqw0rpoA1pJgQvV/tAQ2sNqyYAtaQYkIwiQSB1YYVU8AaUkwIJpEgsNqwYgpYQ4oJwSQSBFYbVkwBa0gxIVKTiLU9CoDVhhVTwBpSTIjUJCKEEFGSkoijswJgtWHFFLCGFBOCSSQIrDasmALWkGJCMIkEgdWGFVPAGlJMCCaRILDasGIKWEOKCcEkEgRWG1ZMAWtIMSF6T6JAwYn2RBtHagmN0MKJJW8Wv9Vm7XHHcpHuK6IrrHILu+l8Ytuv7XEzLoGFWj0WhVirTSyhoV/VXqeWCezhpMjY48YKAsGFQd4yiWZwFu5yUZ5EH8edCqC2q4L0mkTpJTSCy5UVNDDYw0mRs8eNZRlDi6UxibaYRGb4yC7ZuM0kyiihEV44Mb+FwR5OitwkUquchhaQZRJtsLaHcPRkJRHqNFF5CY2M0Zn3xmIxCNKT6HraxxaVDC6D+vZJ1Hltj2m12HmiSDaVNtgnCpbQ8E8uMomW20df75RJ5GebtT2mDHrOU+tzRkwiRaBxpJfQMJd05ejMv8c5OguzzdoerhX1QWp74JB5Asg3E6TfwhnrwB6PzlinrORcLNaYF/aJRFgzY60nD0ptDyiCX/HIKKEROPtTVssE9nBSlO7x8Fl8bVLJ/fsascbUSKIua3sYvaC2ozPYOWuPdryEhr9ORp1aJrCHk2LFZ0/gm43qCcqbCuymq5FEvdb2QPhmIzSw2rBiClhDignR+9Ue0MBqw4opYA0pJgSTSBBYbVgxBawhxYRgEgkCqw0rpoA1pJgQTCJBYLVhxRSwhhQTIjWJWNujAFhtWDEFrCHFhEhNIkIIESUpiTg6KwBWG1ZMAWtIMSGYRILAasOKKWANKSYEk0gQWG1YMQWsIcWEYBIJAqsNK6aANaSYEEwiQWC1YcUUsIYUE6LzJHItnQJy+eu9sLaHfs/yMs7FddDWxeZpV0qHxYxLRAOFRxwrBqy6LjfNsHZtj1pi3qYYKtZSsfCGT2zrtT20i19nxBYoCu5+tE06E0siZ20PRz4ZabNYQ2a/j66DnS5mLZOhx1rgLvu+ooIjiYa1a3vUEvM2xfBywNE/Xy1W7yVEqdYnstYrYhLdC2t7xJb7Mt7y9MeXMfNgyzjOAwsehtZCdK98kk6G4craHrXEMpIo+Caqi9V7CVEqJdHHcddkfaI+N+6Eu7aH7x3Nt2uPeDyFFmZpGyTSJ8pbnFVkubrcJCqv7VFLrMsk2kBtDxPZFRuz5olwZonKanvEl4Wf/6cSyPhPQnsOiQWWxPLcJfFxkJ5Ea2t71BLzNkXk0dkGanvoyFcc2mCfyFfbIyGJ7s/R2Dwqe0aRPk4rFFOoQ2v5jOZdr08i99xr+yTy9onis8Ut+0Rd1/bQkO4QbTKJvGeA4qOz+zNwrOmh6ZakJpJxxjdluNZ4dLbwcdwONDpzT6EBJxF2bQ/FS0owbi6JArU9rOPp0W7N5ns97YdxHK1GtB/HxM+q4Blfx8n5x1yU567WM9ZBfcQZ6zqlolLFQi+xhdoeD2SrC200iYK1PRaD98dHkmvh88UXixIbTGTsY7VNvcCD+66mZ/GX/uhn8V05DZFEfdb2UEEkPDLbXhKFa3tMmD3i8WSn0bI15BTMCbbawDcDg2UzXvjNxuBhEPlm48pRRp1zZ+7d1zyJeq3t8XHcvaJDFEoibOpqXy+XWpGLvz1hDSkmROdXe2ADqw0rpoA1pJgQTCJBYLVhxRSwhhQTgkkkCKw2rJgC1pBiQjCJBIHVhhVTwBpSTIjUJGJtjwJgtWHFFLCGFBMiNYkIIUQOX0IZSVQx+QghJB0mESGkPUwiQkh7mESEkPYwiQgh7WESEULawyQihLSHSUQIaQ+TiBDSHiYRIaQ9TCJCSHv+D2YcRI2oPM+qAAAAAElFTkSuQmCCAA==

    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

  • 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.

  • 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.

  • 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)

  • 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.

  • 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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply