query to select distinct

Question

Hallo Friends,

I need a query to throw an error message on comparison with table temp_invoice.

It should compare the field [Task resp#] , against the [PB-ID] field in PB_Responsbility table for customer = 3D

temp_invoice format

Concession Number   [Task resp#]

   TH-123                 12345
   TH-014                 98065
   TH-123                 41027
   TH-567                 12345
   TH-111                 99765
   TH-567                 41027             
   TH-228                 73095             
   TH-001                 09129             
   TH-779                 41027         
   TH-333                 01029     
   TH-722                 03489  

This should check against the database table for which the customer is 3D.

TABLE PB_Responsbility

[PB-ID]                      Customer
  09129                        3D
  98065                        AB
  12345                        DC
  41027                        ZH
  99765                        3D
  73095                        UZ

If the customer is not 3D OR if [Task resp#] is not found in table PB_Responsbility, it should throw a message, by selecting distinct [Task resp#] from the excel sheet.

Here the required output is, "Incorrect [Task resp#]:- 12345,98065,41027,73095,01029,03489.

I have written the query so,

DECLARE @pb_id_msg VARCHAR(MAX)
SET @pb_id_msg = ''

SELECT @pb_id_msg =  @pb_id_msg + ' ,'
+ CONVERT(VARCHAR(10),[Task resp#]) FROM temp_invoice WHERE [Task resp#]  NOT IN 
    (SELECT [PB-ID] FROM PB_Responsbility WHERE Customer = '3D')
   GROUP BY CONVERT(VARCHAR(10),[Task resp#]).

This is producing the result as required. But if i modify the query with DISTINCT

SELECT DISTINCT @pb_id_msg =  @pb_id_msg + ' ,'
+ CONVERT(VARCHAR(10),[Task resp#]) FROM temp_invoice WHERE [Task resp#]  NOT IN 
    (SELECT [PB-ID] FROM PB_Responsbility WHERE Customer = '3D')

This results in only 1 value. Please explain me, why the second select query with distinct is not working??

Answer 1

Looks like SQL Server interprets distinct to mean you'd only like to assign a value to the variable once.

You can fix that by moving the distinct to a subquery:

SELECT  @pb_id_msg =  @pb_id_msg + ' ,' + CONVERT(VARCHAR(10),[Task resp#]) 
FROM    (
        SELECT  DISTINCT [Task resp#]
        FROM    temp_invoice 
        WHERE   [Task resp#]  NOT IN 
                (
                SELECT  [PB-ID] 
                FROM    PB_Responsbility 
                WHERE   Customer = '3D'
                )
        ) as SubQueryAlias