question

Peter001 avatar image
Peter001 asked

Existence of a value for specific column in table

I have two tables, Driver and Application. In the Driver table for a particular part type and Weightage IN ( 100 AND 199), there is an attribute name. The value for that specific attribute should not be NULL or blank in the application table. If it is blank or NULL then those 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 */ The code I have tried is not giving correct results: 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 999 Thanks
sql-server-2008t-sqlnull
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
null = null is neither true nor false. If you are looking for D.AttributeName to be null AND any of the A.Description* values to be null, you need to evaluate them independently. WHERE D.AttributeName IS NULL AND (A.Description_1 IS NULL OR A.Description_2 IS NULL OR A.Description_3 IS NULL) AND D.Weightage BETWEEN 100 AND 999
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks KenJ
0 Likes 0 ·
Glad to help
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.