CODEWITHSUNDEEP
sqlt-sql
Rajee Kasthuri
Rajee Kasthuri

Reputation: 159

Complex Grouping and Resultset

I need to test two conditions:

  1. How many mysku is not having overtype as 'REP' but having catagory same available in table2 of column [mysku_catagory]

  2. condition: how many COMPETETOR_SKU present in mysku column does not contain CATAGORY present in COMPETETOR_SKU_CATAGORY available in table2.

Sample data: 

DECLARE @table1 TABLE
(
 MYSKU VARCHAR (20),    
 CATAGORY VARCHAR (20), 
 OVERTYPE VARCHAR (20), 
 COMPETETOR_SKU VARCHAR (20)
)

Insert @table1


SELECT 'AAA19-1405',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-1505',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-1508',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2303',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2305',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2308',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-2405',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4003',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4005',    'Cold Air Intake',  'REP',  'MCN8LTC8K' UNION ALL
SELECT 'AAA19-4103',    'Cold Air Intake',  'REC',  'MCN8LTC8K' UNION ALL
SELECT 'MCN8LTC8K',    'Air Filter Wrap',  'REP',  'MCN8AWCC3' UNION ALL
SELECT 'MCN8LTC8K',    'Air Filter Wrap',  'REP',  'MCN8AWCC3' UNION ALL
SELECT 'MCN8LTC8K',    'Catchmehere'    ,  'REP',  'KZNWRTY65' UNION ALL
SELECT 'MCN8LTC8K',     'I M HERE TOO' ,  'REP',  'SDREWTY345'



declare @table2 table
(
mysku_catagory VARCHAR (50),    
COMPETETOR_SKU_CATAGORY VARCHAR (50)

)
INSERT @table2

SELECT 'Cold Air Intake',   'Air Filter%'

Expected Output:

--'AAA19-1505', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2305', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2308', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-2405', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-4003', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'AAA19-4103', 'Cold Air Intake',  'REC',  'MCN8LTC8K'

--'MCN8LTC8K',    'Catchmehere'    ,  'REP',  'KZNWRTY65'

--'MCN8LTC8K',     'I M HERE TOO' ,  'REP',  'SDREWTY345'

Upvotes: 3

Views: 53

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

SELECT *
FROM   @table1 AS t1
WHERE  ( t1.OVERTYPE <> 'REP'
         AND EXISTS(SELECT *
                    FROM   @table2 AS t2
                    WHERE  t2.mysku_catagory = t1.CATAGORY) )
        OR ( NOT EXISTS(SELECT *
                        FROM   @table2 AS t2
                        WHERE  t1.CATAGORY LIKE t2.COMPETETOR_SKU_CATAGORY)
             AND ( t1.OVERTYPE = 'REP'
                   AND NOT EXISTS(SELECT *
                                  FROM   @table2 AS t2
                                  WHERE  t2.mysku_catagory = t1.CATAGORY) ) )

Upvotes: 2

Related Questions