Left Join from two different database

I have sql server 2014. We created a new database for new year and with same tables about last year because we use sql for accounting registration. I have a query about left join on 2015 database with 3 different tables. This year i build the same query with same tables on 2016 database. But i need to collect 2015 tables and 2016 tables to see my whole data with my query. And how can i do it?

This is my query about 2015

SELECT 2015_CARD.LOGISS, 2015_CARD.FOLIO, 2015_CARD.OWING, 2015_CARD.DUEDATE, 2015_CARD.AMOUNT, 2015_CARD.CURRSTAT, 2015_CARD.BANKNAME, 2015_CARD.CITY,2015_RANS.DATE_, 2015_RANS.CARDREF, 2015_RANS.CSREF, 2015_CLC.LOGISS, 2015_CLC.CODE, 2015_CLC.DEFINITION_, 2015_CLC.ADDR1, 2015_CLC.ADDR2, 2015_CLC.TOWN, 2015_CLC.CITY, 2015_CLC.TELNRS1, 2015_CLC.TELNRS2, 2015_CLC.TCKNO, 2015_XT.PARLOGREF, 2015_XT.gsmno, 2015_XT.gsmno2 FROM 2015_CARD
LEFT JOIN
2015_RANS
ON 2015_RANS.CSREF = 2015_CARD.LOGISS
LEFT JOIN
2015_CLC
ON 2015_CLC.LOGISS = 2015_RANS.CARDREF
LEFT JOIN
2015_XT
ON 2015_XT.PARLOGREF = 2015_CLC.LOGISS

And this is my query about 2016

SELECT 2016_CARD.LOGISS, 2016_CARD.FOLIO, 2016_CARD.OWING, 2016_CARD.DUEDATE, 2016_CARD.AMOUNT, 2016_CARD.CURRSTAT, 2016_CARD.BANKNAME, 2016_CARD.CITY,2016_RANS.DATE_, 2016_RANS.CARDREF, 2016_RANS.CSREF, 2016_CLC.LOGISS, 2016_CLC.CODE, 2016_CLC.DEFINITION_, 2016_CLC.ADDR1, 2016.ADR2, 2016_CLC.TOWN, 2016_CLC.CITY, 2016_CLC.TELNRS1, 2016_CLC.TELNRS2, 2016_CLC.TCKNO, 2016_XT.PARLOGREF, 2016_XT.gsmno, 2016_XT.gsmno2 FROM 2016_CARD
LEFT JOIN
2016_RANS
ON 2016_RANS.CSREF = 2016_CARD.LOGISS
LEFT JOIN
2016_CLC
ON 2016_CLC.LOGISS = 2016_RANS.CARDREF
LEFT JOIN
2016_XT
ON 2016_XT.PARLOGREF = 2016_CLC.LOGISS

And i wonder how can i collect this two query in one query and collect my data and view it.

if you want to merge results of tow queries you must use UNION or UNION ALL

SELECT * FROM
(
   SELECT 2015_CARD.LOGISS, 2015_CARD.FOLIO, 2015_CARD.OWING, 2015_CARD.DUEDATE,   2015_CARD.AMOUNT, 2015_CARD.CURRSTAT, 2015_CARD.BANKNAME, 2015_CARD.CITY,2015_RANS.DATE_, 2015_RANS.CARDREF, 2015_RANS.CSREF, 2015_CLC.LOGISS, 2015_CLC.CODE, 2015_CLC.DEFINITION_, 2015_CLC.ADDR1, 2015_CLC.ADDR2, 2015_CLC.TOWN, 2015_CLC.CITY, 2015_CLC.TELNRS1, 2015_CLC.TELNRS2, 2015_CLC.TCKNO, 2015_XT.PARLOGREF, 2015_XT.gsmno, 2015_XT.gsmno2 FROM 2015_CARD
   LEFT JOIN
   2015_RANS
  ON 2015_RANS.CSREF = 2015_CARD.LOGISS
  LEFT JOIN
  2015_CLC
  ON 2015_CLC.LOGISS = 2015_RANS.CARDREF
  LEFT JOIN
  2015_XT
  ON 2015_XT.PARLOGREF = 2015_CLC.LOGISS
) q1

UNION

(
   SELECT 2016_CARD.LOGISS, 2016_CARD.FOLIO, 2016_CARD.OWING, 2016_CARD.DUEDATE, 2016_CARD.AMOUNT, 2016_CARD.CURRSTAT, 2016_CARD.BANKNAME, 2016_CARD.CITY,2016_RANS.DATE_, 2016_RANS.CARDREF, 2016_RANS.CSREF, 2016_CLC.LOGISS, 2016_CLC.CODE, 2016_CLC.DEFINITION_, 2016_CLC.ADDR1, 2016.ADR2, 2016_CLC.TOWN, 2016_CLC.CITY, 2016_CLC.TELNRS1, 2016_CLC.TELNRS2, 2016_CLC.TCKNO, 2016_XT.PARLOGREF, 2016_XT.gsmno, 2016_XT.gsmno2 FROM 2016_CARD
   LEFT JOIN
   2016_RANS
   ON 2016_RANS.CSREF = 2016_CARD.LOGISS
   LEFT JOIN
   2016_CLC
   ON 2016_CLC.LOGISS = 2016_RANS.CARDREF
   LEFT JOIN
   2016_XT
   ON 2016_XT.PARLOGREF = 2016_CLC.LOGISS
) q2

Note:

UNION will remove duplicate rows but UNION ALL not.

1 Like

Thank you so much my brother. i hope everything will nice in your life as your beautiful name.

Is it NEW Database per year or NEW table per year ?

From your query , it is new table.

Anyway, bad design. If you are able to change the design, consider having a year column as part of the Primary key in your table.

1 Like

So true :+1: