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....
精彩评论