I'm having some trouble with the below query. The first part runs correctly. see below; I had to change some of the table names etc. since I was posting this

SELECT DISTINCT
derivedtbl_1.MANIFESTNO, derivedtbl_1.INVOICENO, derivedtbl_1.INVOICEDATE, derivedtbl_1.SYSCOST, derivedtbl_1.SYSSELL, derivedtbl_1.DEST,
derivedtbl_1.SEQ, derivedtbl_1.XFROM OPENQUERY(LinkedServer,
'SELECT x.x AS DEST
,x.x AS MANIFESTNO
,x.x AS INVOICENO
,x.x AS INVOICEDATE
,x.x AS SYSCOST
,x.x AS SYSSELL
,x.x AS SEQ
,x.x AS X

FROM Table LEFT OUTER JOIN
x ON x.x = x.x
LEFT OUTER JOIN x ON x.x = x.x
LEFT OUTER JOIN x ON x.x = x.x

WHERE

x.x BETWEEN 20120101 AND 20120131
AND
x.x= ''**''
AND
x.x= ''022''')
AS derivedtbl_1 LEFT OUTER JOIN
DrumSamples ON derivedtbl_1.MANIFESTNO = DrumSamples.MANIFEST
GROUP BY derivedtbl_1.MANIFESTNO, derivedtbl_1.INVOICENO, derivedtbl_1.INVOICEDATE, derivedtbl_1.SYSCOST, derivedtbl_1.SYSSELL, derivedtbl_1.DEST,
derivedtbl_1.SEQ, derivedtbl_1.X
ORDER BY derivedtbl_1.MANIFESTNO

Returns the below data - which is correct

000039111 202213317 20120126 0.07 25.00
000039111 202213317 20120126 15.00 26.60
000039111 202213317 20120126 39.82 59.73
000039111 202213317 20120126 40.00 25.00


Now as soon as I ask for another field called PCODE from the DrumSamples table, it returns 16 records instead of 4. I have tried several different ways to try and stop this but can't seem to get it to work correctly.

See below

000039111 202213317 20120126 0.07 25.00 1234
000039111 202213317 20120126 15.00 26.60 2012
000039111 202213317 20120126 39.82 59.73 4567
000039111 202213317 20120126 40.00 25.00 0001

000039111 202213317 20120126 0.07 25.00 1234
000039111 202213317 20120126 15.00 26.60 2012
000039111 202213317 20120126 39.82 59.73 4567
000039111 202213317 20120126 40.00 25.00 0001

000039111 202213317 20120126 0.07 25.00 1234
000039111 202213317 20120126 15.00 26.60 2012
000039111 202213317 20120126 39.82 59.73 4567
000039111 202213317 20120126 40.00 25.00 0001

000039111 202213317 20120126 0.07 25.00 1234
000039111 202213317 20120126 15.00 26.60 2012
000039111 202213317 20120126 39.82 59.73 4567
000039111 202213317 20120126 40.00 25.00 0001


any help would be appreciated.

Thanks in advance

Lazy man's answer is just add a SELECT DISTINCT...

You're joining on a table where you're missing a filter which will restrict the number of rows back to 4, otherwise you actually do have 16 distinct rows, but cannot notice because you're not asking for any of the data which would show you it was different (which leads back to the lazy man's answer).

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

I tried adding DISTINCT in the below section as well. as get the same result 16 rows not 4. any other ideas ?

'SELECT DISTINCT
x.x AS DEST
,x.x AS MANIFESTNO
,x.x AS INVOICENO
,x.x AS INVOICEDATE
,x.x AS SYSCOST
,x.x AS SYSSELL
,x.x AS SEQ
,x.x AS X

As you already had a DISTINCT in the first place, and you have every record 4 times, the only reasonable conclusion is, there is another value in each record making it differ from the other fields.

Distinct does not fail. If you want these columns distinct, only query them in your fieldlist and the distinct will work. Distinct will always look on the whole record, so maybe you miss something off screen?

Bye, Olaf.