sql joins query

  • 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

  • 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