March 8, 2007 at 7:37 am
I have the following query:
declare @saleid int
set @saleid = 67026
Set nocount on
Print 'Please Rollback:'
Print ''
Print 'System: Production'
select 'Booking:',cast(Sale.SaleID AS VARCHAR)+ ' - '
+ case
when TerminationInfo.TerminationTypeID
= 1000 then 'StandardTermination'
else ''
End
+ case
when Sale.NoticeReasonID = 9000
then ' - move to ' +
cast(salereverse.saleid as VARCHAR)
else ''
end
+ char(13) + char(10) +
'Centre: ' + Centre.CentreName
+ char(13) + char(10) +
'Company: ' + Company.CompanyName
+ char(13) + char(10) +
'Room(s): ' + Inventory.inventoryname
+ char(13) + char(10) +
+ char(13) + char(10) +
+ case
when Sale.NoticeReasonID = 9000
then '(Moved part ' +
cast(salereverse.saleid as VARCHAR) + ')'
else ''
end
from Sale
inner JOIN company
on Sale.CompanyID = Company.CompanyID and
Sale.companyversion = Company.companyversion
inner JOIN SaleStatus
on Sale.SaleStatusID = SaleStatus.SaleStatusID
inner JOIN TerminationInfo
on Sale.SaleID = TerminationInfo.SaleID
inner JOIN Centre
on Sale.centreID = Centre.CentreID and
Sale.CentreVersion = Centre.CentreVersion
inner JOIN inventorysale
on sale.saleid = inventorysale.saleid
inner JOIN InventoryUsage
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
inner JOIN office
on InventoryUsage.officeID = office.officeid
and InventoryUsage.officeVersion = office.officeVersion
inner JOIN inventory
on Inventoryusage.inventoryID = inventory.inventoryid
inner JOIN sale salereverse
on salereverse.relatedsaleid = @saleid
where sale.saleID = @saleid
Problem is that there will not always be a related record in some of the tables... eg. TerminationInfo, I think that this will make it not bring back any data. is this true? and if so, how do i get around this?
Dion
March 8, 2007 at 10:03 am
ok, ok, ok,
it was left joins, thus:
declare @saleid int
set @saleid = 71476
Set nocount on
Print 'Please Rollback:'
Print ''
Print 'System: Production'
select 'Booking:',cast(Sale.SaleID AS VARCHAR)
+ case
when TerminationInfo.TerminationTypeID
= 1000 then ' - StandardTermination'
else ' - unterminated'
End
+ case
when Sale.NoticeReasonID = 9000
then ' - move to ' +
cast(salereverse.saleid as VARCHAR)
else ''
end
+ char(13) + char(10) +
'Centre: ' + Centre.CentreName
+ char(13) + char(10) +
'Company: ' + Company.CompanyName
+ char(13) + char(10) +
'Room(s): ' + Inventory.inventoryname
+ char(13) + char(10) +
+ char(13) + char(10) +
+ case
when Sale.NoticeReasonID = 9000
then '(Moved part ' +
cast(salereverse.saleid as VARCHAR) + ')'
else ''
end
+ char(13) + char(10)
from Sale
inner JOIN company
on Sale.CompanyID = Company.CompanyID and
Sale.companyversion = Company.companyversion
left JOIN TerminationInfo
on Sale.SaleID = TerminationInfo.SaleID
inner JOIN Centre
on Sale.centreID = Centre.CentreID and
Sale.CentreVersion = Centre.CentreVersion
inner JOIN inventorysale
on sale.saleid = inventorysale.saleid
inner JOIN InventoryUsage
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
inner JOIN office
on InventoryUsage.officeID = office.officeid
and InventoryUsage.officeVersion = office.officeVersion
inner JOIN inventory
on Inventoryusage.inventoryID = inventory.inventoryid
left JOIN sale salereverse
on salereverse.relatedsaleid = @saleid
where sale.saleID = @saleid
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply