SQLTeam.com | Weblogs | Forums

Left Join from two different database

sql2014

#1

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.


#2

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.


#3

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


#4

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.


#5

So true :+1: