Value exist in table or not

Transact-SQL
1

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

2

This should do it

select app.* from @APPLICATION app
LEFT JOIN @REFERENCE ref
ON (app.LINE=ref.Line and ISNULL(app.REMARKS3,'')= ISNULL(ref.POSITION,''))
WHERE ref.Line is null

3

Thank You kazeem,
but i need to check in a group of Year, make,model contain all valid position. may i provide some more sample data.

Thanks

4

i didn't get you. Can you be more specific

5

Can you Please send the desired output you need.

6

Thank You Very Much Kazeem.
This is giving perfect result..... :smile: