Re: remove duplicate items

In article <XnsA9F66F7DD5898ammammatatiscalineti_at_148.251.67.112>, Ammammata <ammammata_at_tiscalinet.it> wrote:
> this query, used in Delivery printout, scans all products in the doc and
> for each gets the corresponding Order number, to be printed in sorted order
> and separated by a slash:
>
> select d.docnum, 'txt' Testo
> into #tmp
> from dln1 a
> inner join dln1 b On a.U_I_BaseEntry = b.docentry And a.U_I_BaseType = 15
> inner join rdr1 c On b.baseentry = c.docentry And b.BaseLine = c.linenum
> And b.BaseType = 17
> inner join ordr d On c.docentry = d.docentry
> where a.docentry = '87'
>
> Select distinct
> STUFF((Select ' / '+ cast(t1.docnum as varchar(100))
> from #tmp T1
> where T1.Testo = T2.Testo
> For XML PATH(''))
> ,1,3,'')
> from #tmp t2
> drop table #tmp
>
> It generates the following:
>
> 6153 / 6153 / 6153 / 6153 / 6153 / 6153 / 6153 / 6153
>
> because the delivery has 8 products, all of them from the same order
>
> how can I reduce it to a single occurrence?

Try using SELECT DISTINCT in the first query too.

[Quoted] Alternatively, try replacing both queries with something like this:

SELECT GROUP_CONCAT(DISTINCT d.docnum ORDER BY d.docnum SEPARATOR ' / ') [Quoted] FROM dln1 a

INNER JOIN dln1 b ON a.U_I_BaseEntry = b.docentry AND a.U_I_BaseType = 15
INNER JOIN rdr1 c ON b.baseentry = c.docentry AND b.BaseLine = c.linenum AND b.BaseType = 17
INNER JOIN ordr d ON c.docentry = d.docentry

Cheers
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org