SQL Server Best way to join two tables one to three possible columns

qlzsbp2j 于 2023-03-28 发布在其他

关注(0)|答案(2)|浏览(191)

I'm trying to join two tables by string id but the second table has 3 possible id columns to join. Tried to go with 'or' but the request is really slow and creating a view with 3 copies of the table isn't good anyway either and I don't know how to put all values in one set so the view don't has 3 copies of all needed fields. Is there any better possibility? How can I join 2 tables having only one copy of fields from second table.

Table1
ID DocumentID Value
1  Doc123     54
2  Doxx24     85
3  Doxx24     111
4  Docodoc1   90

Table2
ID2 Document1ID Document2ID Document3ID Value
1  Doc123       null       null        54
2  null         Doxx24     null        85
3  null         Doxx24     null        111
4  null         null       Docodoc1    90

Output
ID DocumentID Value ID2 Document1ID Document2ID Document3ID Value
1  Doc123     54    1  Doc123       null       null        54
2  Doxx24     85    2  null         Doxx24     null        85
3  Doxx24     111   3  null         Doxx24     null        111
4  Docodoc1   90    4  null         null       Docodoc1    90

As you can see the value I need to join is in 3 columns but all columns share the same data the difference is the document type that is nowhere described just other than column name. Table1.DocumentID contains one value from the three DocumentID's from Table2 and I need all the data from table2 if it match.

This is my whole query.

SELECT ledger.ACCOUNTINGDATE, ledger.DOCUMENTNUMBER, ledger.JOURNALNUMBER, ledger.SUBLEDGERVOUCHER, ledger.ACCOUNTINGCURRENCYAMOUNT, ledger.GENERALJOURNALENTRY, ledger.MAINACCOUNTVALUE, 
              ledger.TRANSACTIONCURRENCYCODE, ledger.TRANSACTIONCURRENCYAMOUNT, ledger.Typ_Ksiegowania, ledger.Typ_Transakcji, ledger.JOURNALCATEGORY, ledger.LEDGERACCOUNT, ledger.REPORTINGCURRENCYAMOUNT, 
              ledger.RECID AS ledgerREcid, ledger.CREATEDTRANSACTIONID, ledger.MAINACCOUNTNAME, ledger.PARTITION, trans.ITEMID, trans.VOUCHERPHYSICAL, trans.VOUCHER, trans.RECID, trans.REFERENCEID, trans.SettelemntVoucher, 
              trans.COSTAMOUNTPHYSICAL, trans.COSTAMOUNTPOSTED, trans.PACKINGSLIPID, trans.QTY, trans.INVENTTRANSID, trans.CURRENCYCODE, trans.INVOICEID, row_number() over (order by ledger.partition) as MyKey
FROM     dbo.TestGeneralTransYear AS ledger LEFT OUTER JOIN
              dbo.SRM_InventTransSettlementTab AS trans ON (ledger.SUBLEDGERVOUCHER = trans.VOUCHER COLLATE Polish_CI_AS or ledger.SUBLEDGERVOUCHER = trans.voucherphysical  COLLATE Polish_CI_AS or ledger.SUBLEDGERVOUCHER = trans.SettelemntVoucher COLLATE Polish_CI_AS)

sql-server

来源：https://stackoverflow.com/questions/75827256/best-way-to-join-two-tables-one-to-three-possible-columns

2条答案

按热度按时间

fafcakar1#

You can do it with a COALESCE approach...

SELECT *
FROM   Table1 AS T1
       JOIN Table2 AS T2
          ON T1.Document_Id = COALESCE(T2.Document1ID, T2.Document2ID, T3.Document3ID)

赞(0）回复(0）举报 2023-03-28

y1aodyip2#

My guess is the fastest would be not to make 3 views on table 2.

create view Table2_view1 as select Document1ID as ID, * where Document1ID is not null
create view Table2_view2 as select Document2ID as ID, * where Document2ID is not null
create view Table2_view3 as select Document3ID as ID, * where Document3ID is not null

then do it as 3 UNIONS and joins

select * from Table1 join Table2_view1
union
select * from Table1 join Table2_view2

My guess is SQL Server would be good at interpreting this fast.

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Best way to join two tables one to three possible columns

2条答案

相关问题

热门标签

最新问答