SQL Select with 3 table from 2 databases

Question

I have three tables with some banners info and i need to select all the banners from all of them by CompanyID.

The tables are:

BannerCompany (AdminMaster.dbo.BannerUnidades)
Banner        (AdminMaster.dbo.Banner)
BannerClient  (AdminHotSite.dbo.Banner)

The first table (BannerCompany) has CompanyID and BannerID and is linked to the table Banner by BannerID;
The second table (Banner) has BannerID and all the rest of the banner info;
The third table (BannerClient) is another table where i have other banners also with CompanyID and BannerID (This BannerID from table BannerClient is different from the BannerID from table Banner).

What i need is select the banners on BannerCompany and link them with Banner by BannerID with a WHERE on BannerCompany.BannerCompanyID and then select the banners from BannerClient with the same where but now on BannerClient.BannerCompanyID.

Now, i can select the banners from the first and the second but i can't find a way to put the third table on the game.

All the tables have the same columns with the same name.

My code so far:

SELECT * FROM AdminMaster.dbo.BannerUnidades AS bUnidade
INNER JOIN AdminMaster.dbo.Banner  AS bMaster ON bUnidade.BannerID = bMaster.BannerID
INNER JOIN AdminHotSite.dbo.Banner AS bHS     ON bHS.BannerPrincipal = 1
WHERE bHS.UnidadeCodEmitente = 45471

Thanks in advance.

are the two different DB servers linked?btw which tables are on which db? your question does not indicate this — gh9, Jun 25 '14 at 15:11
The first two tables are on one DB and the third is in another. When i try it, it only bring me records from the first or the second when i change my WHERE. — Ariel, Jun 25 '14 at 15:18
oh perfect. Put your sql query up i will post some pseudo code that will do what you want — gh9, Jun 25 '14 at 15:30
I need to join the columns, i receive the results from the first and second table and then i have all the columns again for the third table. http://imagizer.imageshack.us/a/img841/2731/8gxq.jpg — Ariel, Jun 25 '14 at 16:28

Ilya Berdichevsky · Accepted Answer · 2014-06-25T18:10:36.527

I think this will give you a starting point.

Get list of all banners for the specific company:

SELECT bMaster.BannerID, bHS.BannerTitulo 
FROM AdminMaster.dbo.BannerUnidades AS bUnidade
INNER JOIN AdminMaster.dbo.Banner  AS bMaster ON bUnidade.BannerID = bMaster.BannerID
WHERE bUnidade.UnidadeCodEmitente = 45471

UNION ALL

SELECT bHS.BannerID, bHS.BannerTitulo 
FROM AdminHotSite.dbo.Banner AS bHS
WHERE bHS.UnidadeCodEmitente = 45471 AND bHS.BannerPrincipal = 1

Get list of all banners for the specific company with custom ORDER BY:

SELECT bMaster.BannerID, bHS.BannerTitulo, SortOrder = 1, BannerOrdem 
FROM AdminMaster.dbo.BannerUnidades AS bUnidade
INNER JOIN AdminMaster.dbo.Banner  AS bMaster ON bUnidade.BannerID = bMaster.BannerID
WHERE bUnidade.UnidadeCodEmitente = 45471

UNION ALL

SELECT bHS.BannerID, bHS.BannerTitulo, SortOrder = 2,  BannerOrdem 
FROM AdminHotSite.dbo.Banner AS bHS
WHERE bHS.UnidadeCodEmitente = 45471 AND bHS.BannerPrincipal = 1
ORDER BY SortOrder, BannerOrdem

What if i want to order the result on both selects ? Both tables have a column called BannerOrdem and i want first order by the first select and then for the second — Ariel, Jun 25 '14 at 17:22
Look here on how to use ORDER BY in UNION: http://stackoverflow.com/q/213851/327165 — Ilya Berdichevsky, Jun 25 '14 at 18:06

SQL Select with 3 table from 2 databases

1 Answers1