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