开发者

SELECTING DISTINCT Records

My question is LAME. I KNOW! But I cannot figure it out, yet! Here is what I want to do: I have a set of records which is duplicate at 'JobNumber' and 'JobID'. Here is my query that is selecting the records:

SELECT  dbo.InvoiceLineDetail.JobNumber,
    dbo.InvoiceLineDetail.PatientName,
    dbo.InvoiceLineDetail.CustomerAccountName AS Name,
    COALESCE(InvoiceLineDetail.ShipAddressAddr2 + ' ',
        '') + COALESCE(InvoiceLineDetail.ShipAddressAddr3+' ',
        '') + COALESCE(InvoiceLineDetail.ShipAddressAddr4 +' ',
        '') + coalesce(InvoiceLineDetail.ShipAddressCity +' ',
        '') + COALESCE(InvoiceLineDetail.ShipAddressState +' ',
        '') + COALESCE(InvoiceLineDetail.ShipAddressCountry,
        '') as [Address],
     dbo.InvoiceLineDetail.ShipAddressPostalCode AS ZipCode,
     dbo.JobStatus.JobTableId AS JobID,
     dbo.JobStatus.OrderType,
     dbo.JobStatus.ShipTrackingNumber as Tracking#,
     dbo.JobStatus.ShipMethodTransmitted as TransmitMethod,
     dbo.JobStatus.DateShipTransmitProcessed as DateProcessed,
     dbo.JobStatus.ShipmentProcessedBy as ProcesssedBy,
    dbo.JobStatus.Critical,
    dbo.View_JobsToShipCount.JobsToShip,
    dbo.JobStatus.ShipTransmitStatus as Status,
    dbo.J开发者_StackOverflowobStatus.InvoiceStatus,
    dbo.InvoiceLineDetail.Quantity
FROM dbo.View_JobsToShipCount 
LEFT OUTER JOIN dbo.InvoiceLineDetail 
    ON dbo.View_JobsToShipCount.Name = dbo.InvoiceLineDetail.CustomerAccountName 
LEFT OUTER JOIN dbo.JobStatus 
    ON dbo.InvoiceLineDetail.JobID = dbo.JobStatus.JobTableId

WHERE (dbo.InvoiceLineDetail.ChargeGroup = 'LENS') and ((dbo.JobStatus.InvoiceStatus = N'IR') OR
  (dbo.JobStatus.ShipTransmitStatus = N'ReadyToShip'))

Now I want JobNumber and JobID as UNIQUE values. I am getting same records for both.

Thanks for your help :)


If all the other information is the same, simply add a "DISTINCT" to your query.

NOTE: Table aliases REALLY help with readability....

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜