Home Forums SQL Server 2008 T-SQL (SS2K8) How to combine two tables when one table is in different database and this table will determine the value to be shown on the first table. RE: How to combine two tables when one table is in different database and this table will determine the value to be shown on the first table.

  • January 15, 2013 at 3:19 am

    #1577217

    marksquall (1/14/2013Just one last thing, how would I add an additional column called [font="Courier New"]CameFromArchive[/font] so that the database administrator would determine what [font="Courier New"]CtrlNo[/font] came from the archive table (0-NO 1-YES)?

    I think you can use a case just like the one that was allready included in the query posted by Sean, like so :

    SELECT

    A.ConsInvNumber AS [Consolidated Invoice No],

    A.InvTrackNo AS [Invoice TrackNo],

    B.SaleOrderNo AS [Sales OrderNo],

    B.OrderLine AS [Sales Order LineNo],

    case when A.CtrlNo = 0 then osa.CtrlNo else a.CtrlNo end as CtrlNo,

    case when OSA.CtrlNo is NULL then 'No' else 'Yes' end as CameFromArchive

    FROM DBMain.dbo.ConsInvoiceDetail AS A INNER JOIN

    DBMain.dbo.IndInvDetail AS B ON A.InvTrackNo = B.InvTrackNo

    Left Join (select top 1 CtrlNo from DBArchive.dbo.OrderSummaryArchive where SalesOrderNo = B.SaleOrderNo) osa

    WHERE A.ConsInvNumber=980029768

    EDIT: forgot a comma

    MCSA, MCITP
    SQL Server / Windows Server (2008)