SQL query problem, listing all deliveries linked to an invoice

[Former Member]

Hi I'm trying to write a query that returns all the deliveries linked to an invoice as a single commaseparated result.

This query gets all the links from every line as a commaseparated result but I want to remove the duplicate entries, eg. where/how do I insert the distinct clause?

DECLARE @EmployeeList varchar(350)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +

( T1.baseref )

FROM oinv T0 inner join inv1 T1 on T0.DocEntry = T1.DocEntry

where T0.docnum = 119229 and T1.baseref <> ''

SELECT @EmployeeList

Thanks in advance for all help

[Former Member · Accepted solution]

Hi Torbjörn,

How about creating a temporary table in which you would select all the distinct baseref values for the selected invoice and then making the coalesce selection against this temporary table instead of inv1 ?

Henry

[Former Member · Accepted solution]

Thanks for the help Henry,

using a temporary table solved my problem.

Torbjørn

[Former Member]

Hi,

How did you use the temporary table?

Can you write your code here.

I am also getting repeated data values in my query output. I want to save that query output into one temporary table and than want to use the SELECT DISTINCT on that temporary table. I am searching on forum madely to do that but can't find the perfect help. It will be nice if you can help me in that.

Thank you