Hello Techie,
Please help me on this.
There are Two Tables
TABLE 1 Called as app table
TABLE 2 called as reference table
i need to check in app table whether a group of make,model ,year contains only those remark
which are present in reference table for same line
so join will be on column called as line
Table Structure
DECLARE @REFERENCE TABLE
(
Line VARCHAR (50),
PositionID INT,
POSITION VARCHAR (50)
)
INSERT @REFERENCE
SELECT 'ABSWLSPSRG', '1', 'N/A' UNION ALL
SELECT 'ABSWLSPSRG', '2', 'Left' UNION ALL
SELECT 'ABSWLSPSRG', '12', 'Right' UNION ALL
SELECT 'ABSWLSPSRG', '22', 'Front' UNION ALL
SELECT 'ABSWLSPSRG', '30', 'Rear' UNION ALL
SELECT 'ACCESSORYD', '2337', 'Alternator' UNION ALL
SELECT 'ACCESSORYD', '2338', 'Arbor ' UNION ALL
SELECT 'ACCESSORYD', '2529', 'Engine To Variator' UNION ALL
SELECT 'ACCESSORYD', '2530', 'Engine To Vari-Speed ' UNION ALL
SELECT 'BUMPERCOVE', '93', 'Rear Right Lower' UNION ALL
SELECT 'BUMPERCOVE', '93', 'Rear' UNION ALL
SELECT 'BUMPERCOVE', '94', 'Rear Right Upper' UNION ALL
SELECT 'BUMPERCOVE', '90', 'Front' UNION ALL
SELECT 'BUMPERCOVE', '91', 'Rear Left Upper' UNION ALL
SELECT 'BUMPERCOVE', '90', 'Front Right Lower' UNION ALL
SELECT 'CTRLARMBSH', '6' , 'Left Outer' UNION ALL
SELECT 'CTRLARMBSH', '2' , 'Left' UNION ALL
SELECT 'CTRLARMBSH', '5' ,'Left Inner' UNION ALL
SELECT 'CTRLARMBSH', '1', 'N/A '
DECLARE @APPLICATION TABLE
(
LINE VARCHAR (50),
MAKE VARCHAR (50),
MODEL VARCHAR (50),
YEAR VARCHAR (50),
REMARKS3 VARCHAR (50)
)
INSERT @APPLICATION
SELECT 'ABSWLSPSRG', '42', '356', '2007', NULL UNION ALL
SELECT 'BUMPERCOVE', '40', '303', '2005', 'Front' UNION ALL
SELECT 'BUMPERCOVE', '40', '303', '2005', 'Front' UNION ALL
SELECT 'BUMPERCOVE', '40', '303', '2005', 'Rear' UNION ALL
SELECT 'CTRLARMBSH', '74', '79', '1995', 'Rear' UNION ALL
SELECT 'CTRLARMBSH', '74', '79', '1995', 'Rear' UNION ALL
SELECT 'CTRLARMBSH', '74', '79', '1996', 'Rear' UNION ALL
SELECT 'CTRLARMBSH', '74', '79', '1996', 'FRONT' UNION ALL
SELECT 'CTRLARMBSH', '74', '79', '1999', 'Left Inner'
--OUTPUT
/*
'ABSWLSPSRG', '42', '356', '2007', NULL -- as NULL Position not available in REFERENCE table for a given line.
'CTRLARMBSH', '74', '79', '1995', 'Rear' -- as Rear Position not available in REFERENCE table for a given line.
'CTRLARMBSH', '74', '79', '1995', 'Rear'-- as Rear Position not available in REFERENCE table for a given line.
'CTRLARMBSH', '74', '79', '1996', 'Rear'-- as Rear Position not available in REFERENCE table for a given line.
'CTRLARMBSH', '74', '79', '1996', 'FRONT' -- as FRONT Position not available in REFERENCE table for a given line.
'CTRLARMBSH', '74', '79', '1999', 'Left Inner'-- as Left Inner Position not available in REFERENCE table for a given line.
*/
Thank You Very Much