Existence of a value for specific column in table

Transact-SQL
1

Respected Techie,
May someone please help me how to acheive this critical scenario Please.

I have two tables Driver and Application.

In Driver table for a particular parttype and Weightage IN ( 100 AND 199), there is an attribute name,
There will be value for that specific attrubute which should not be NULL or blank in application table.
if it is blank or NULL then records should be in Output.

Table Structure

DECLARE @DRIVER TABLE
(
PartType VARCHAR (50),
AttributeName VARCHAR (50),
Weightage VARCHAR (50)
)

INSERT @DRIVER

SELECT 'Air Filter', 'Shape', '100' UNION ALL
SELECT 'Fender Flare', 'Color Group', '500' UNION ALL
SELECT 'Tonneau Cover', 'Lead Type', '999' UNION ALL
SELECT 'Lug Bolt', 'Thread Size', '100'

SELECT * FROM @DRIVER

DECLARE @application TABLE
(
Part_Number VARCHAR (50),
Part_Type VARCHAR (50),
Description_1 VARCHAR (50) ,
Description_1_Value VARCHAR (50),
Description_2 VARCHAR (50),
Description_2_Value VARCHAR (50),
Description_3 VARCHAR (50),
Description_3_Value VARCHAR (50)

)

Insert @application

SELECT 'AAA19-1405', 'Air Filter', 'Shape', 'Universal', 'Number of Pieces', '2', 'Design', 'No Logo' Union All
SELECT 'ZORLYM12', 'Air Filter', 'Shape', '', 'Number Of pieces', '4', 'Design', 'No Logo' Union All
SELECT 'AAA19-1508', 'Fender Flare', 'Type', 'Universal', 'Color Group', 'Red', 'Design', 'No Logo' Union All
SELECT 'NORFLEX89', 'Fender Flare', 'Type', 'Universal', 'Color Group', NULL, 'Design', 'No Logo' Union All
SELECT 'AAA19-2305', 'Lug Bolt', 'Type', 'Universal', 'Number Of Pieces', '4', 'Thread Size', 'MAX' UNION ALL
SELECT 'BIOCONINSULIN', 'Lug Bolt', 'Type', 'Universal', 'Number Of Pieces', '4', 'Thread Size', NULL UNION ALL
SELECT 'BBB19-2305', 'Lug Bolt','Lead Type', NULL, 'Number Of Pieces', '4', 'Thread Size', 'MIN' union all
SELECT 'XXXXXX', 'Air Filter', 'Shape', 'CATCH', 'Number Of Pieces', '4', 'Design', NULL

Expected Output

/*
ZORLYM12 Air Filter Shape Number Of pieces 4 Design No Logo
NORFLEX89 Fender Flare Type Universal Color Group NULL Design No Logo
BIOCONINSULIN Lug Bolt Type Universal Number Of Pieces 4 Thread Size NULL

*/

Code I tried but not giving correct result:

SELECT *
FROM @DRIVER D
INNER JOIN @application A ON A.Part_Type = D.PartType
WHERE
(D.AttributeName = A.Description_1 OR
D.AttributeName = A.Description_2 OR
D.AttributeName = A.Description_3)
AND (A.Description_1_Value IS NULL OR
A.Description_2_Value IS NULL OR
A.Description_3_Value IS NULL)
AND d.Weightage BETWEEN 100 AND 600

2 
WHERE    
(
 (
        D.AttributeName = A.Description_1     
 AND    (A.Description_1_Value    IS NULL    OR    A.Description_1_Value = '')
 )
 OR    
 (
        D.AttributeName = A.Description_2     
 AND    (A.Description_2_Value    IS NULL    OR    A.Description_2_Value = '')
  )
  OR
  (
        D.AttributeName = A.Description_3     
  AND    (A.Description_3_Value    IS NULL    OR    A.Description_3_Value = '')
  )
 )
 AND D.Weightage BETWEEN 100 AND 600