Check Invalid data

Transact-SQL
1

Respected Techie,

i have two table

DECLARE @WAREHOUSE TABLE
(
MOUNTINGTYPE VARCHAR (50),
NOTEID VARCHAR (50)

)

INSERT @WAREHOUSE

SELECT 'CLDAIR', 'E006' UNION ALL
SELECT 'CLDAIR', 'E007' UNION ALL
SELECT 'CLDAIR', 'E009' UNION ALL
SELECT 'CLDAIR', 'E010' UNION ALL
SELECT 'HOTAIR', 'A001' UNION ALL
SELECT 'HOTAIR', 'A002' UNION ALL
SELECT 'HOTAIR', 'A003' UNION ALL
SELECT 'HOTAIR', 'E010'

SELECT * FROM @WAREHOUSE

DECLARE @MART TABLE
(
MOUNTINGTYPE VARCHAR (50),
SKU VARCHAR (50),
NOTEID VARCHAR (50)

)

INSERT @MART

SELECT 'CLDAIR', 'AEM41-1401C', 'E010,V019,TUF' UNION ALL
SELECT 'CLDAIR', 'AEM41-1401C', 'QW5,E010' UNION ALL
SELECT 'CLDAIR', 'AEM41-1401C', 'TXTY,E010,URT9' UNION ALL
SELECT 'CLDAIR', 'AEM41-1401P', 'E010' UNION ALL
SELECT 'CLDAIR', 'AEM41-1401P', 'HGT,E010' UNION ALL
SELECT 'CLDAIR', 'AEM24-6110C', 'E009' UNION ALL
SELECT 'HOTAIR', 'AEM21-400R', '10AT,A001' UNION ALL
SELECT 'HOTAIR', 'AEM41-1404C', 'A002' UNION ALL
SELECT 'HOTAIR', 'AEM21-400R', 'NULL' UNION ALL
SELECT 'HOTAIR', 'AEM21-401P', 'ZS62' UNION ALL
SELECT 'HOTAIR', 'AEM21-403R', 'PLS3'

SELECT * FROM @MART

Data in warehouse are correct data, for each distinct MOUNTINGTYPE it contain many noteid.
i am trying to check data in the mart.

SKU for particular MOUNTINGTYPE must always contain only those id which is present in warehouse for same mounting type

expected output-- as in warehouse for HOTAIR MOUNTINGTYPE noteid 'ZS62' and 'PLS3' is not available

'HOTAIR', 'AEM21-401P', 'ZS62'
'HOTAIR', 'AEM21-403R', 'PLS3'

Please share your expertise. Thanks

2 
SELECT *
FROM @MART m
WHERE 
    m.NOTEID <> 'NULL' AND
    NOT EXISTS( 
        SELECT 1
        FROM @WAREHOUSE w
        WHERE
            w.MOUNTINGTYPE = m.MOUNTINGTYPE AND
            w.NOTEID = m.NOTEID
    )
1 Like
3

What about the row that has the NULL for NOTEID? You don't want it in the output because NOTEID is null or, is it because there is another row with the same SKU and MOUNTINGTYPE that has a non-null NOTEID that is present in the @WAREHOUSE table?

4

select M.* from @MART M inner join (
SELECT MOUNTINGTYPE,NOTEID FROM @MART
except
SELECT * FROM @WAREHOUSE
) as dt on dt.MOUNTINGTYPE = M.MOUNTINGTYPE and dt.NOTEID = M.NOTEID

1 Like
5

If noteid in mart is comma seperated (edited ), will it possible to achieve the result. please suggest.