Joining across two databases and multiple tables

Question

question

Dave Myers asked Jun 17, '10

Joining across two databases and multiple tables

I am working on a view that requires data from two different databases on the same server. I am using fully qualified names on the joins, but I am having difficulty in setting them up in a functional manner.

I was hoping someone could take a look at this and show me what I can do to get this to work.

SELECT     
  wwwstores.dbo.zSTORE.Store_Number,
  wwwstores.dbo.zSTORE.Store_Name,
  wwwstores.dbo.zSTORE.Street_Address,
  wwwstores.dbo.zSTORE.City,
  wwwstores.dbo.zSTORE.State,
  wwwstores.dbo.zSTORE.Zip_Code,
  wwwstores.dbo.zSTORE.Zip_Ext,
  wwwstores.dbo.zSTORE.Phone_Number,
  wwwstores.dbo.zSTORE.Email_Address,
  wwwstores.dbo.zSTORE.Location,
  wwwstores.dbo.zSTORE.Latitude,
  wwwstores.dbo.zSTORE.Longitude,
  wwwstores.dbo.zSTORE.status,
  wwwstores.dbo.zSTORE.SM_Name,
  wwwstores.dbo.zSTORE.DM_Nmae,
  www.stores.dbo.zSTORE.RVP_Name,
  wwwstores.dbo.Stores.yearopen,
  wwwAdvertising.dbo.StorePaper.Newspaper_ID,
  wwwAdvertising.dbo.Store_Paper.Chain,
  wwwAdvertising.dbo.Store_Paper.Address_Tag,
  wwwAdvertising.dbo.Store_Paper.Ad_Stores,
  wwwAdvertising.dbo.Newslist.adsize,
  wwwAdvertising.dbo.Newslist.primarycontact,
  wwwAdvertising.dbo.Newslist.primaryphone,
  wwwAdvertising.dbo.Newslist.primaryemail,
  wwwAdvertising.dbo.Newslist.Newspaper,
  wwwAdvertising.dbo.Newslist.billing
FROM 
   wwwstores.dbo.Stores 
INNER JOIN wwwstores.dbo.zSTORE 
   ON wwwstores.dbo.Stores.Store = wwwstores.dbo.zSTORE.Store_Number
wwwAdvertising.dbo.Store_Paper 
INNER JOIN wwwStores.dbo.zStore 
   ON wwwAdvertising.dbo.Store_Paper.Store_Number = wwwStores.dbo.zStore.Store_Number
wwwAdvertising.dbo.Newslist 
INNER JOIN wwwStores.dbo.scrapping 
   ON wwwAdvertising.dbo.Newlist.adgroup = wwwStores.dbo.scrapping.adgroup
ORDER BY   
  yearopen DESC

sql-server-2005 select joins view

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman commented · Jun 17, 2010 at 02:39 PM

Before we try to parse a query like that, could you tell us how it is failing?

0 ·

Answer 1 · 2010-06-17T14:41:10Z

dvroman answered Jun 17, '10

First I would use aliases as much as possible to clarify the query. I can't tell what was intended since it appears that you're joining the same table twice. This is possible and I use it, but I always use aliases in that case.
Also there is an error in your joins.

FROM wwwstores.dbo.Stores s
    INNER JOIN wwwstores.dbo.zSTORE zs ON s.Store = zs.Store_Number
    INNER JOIN wwwAdvertising.dbo.Store_Paper sp ON ?????
    INNER JOIN wwwStores.dbo.zStore zs1 ON sp.Store_Number = zs1.Store_Number
    INNER JOIN wwwAdvertising.dbo.Newslist ON ????
    INNER JOIN wwwStores.dbo.scrapping sc ON nl.adgroup = sc.adgroup
ORDER BY yearopen descdesc

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-17T15:01:48Z

Oleg answered Jun 17, '10

A couple of questions are needed to clarify your question.

Is wwwstores.dbo.zSTORE.DM_Nmae spelled correctly or wwwstores.dbo.zSTORE.DM_Name was intended?

Is wwwAdvertising.dbo.StorePaper spelled correctly or it should be wwwAdvertising.dbo.Store_Paper?

What is the relationship between wwwAdvertising.dbo.Store_Paper and wwwAdvertising.dbo.Newslist?

dvroman already answered your question pretty much correctly, and the answer included very useful suggestion to use aliases because they really make queries more readable. I am adding the query below simply because I think I was able to deduce almost everything correctly except the very last join because I don't know how NewsList is related to other tables. I also excluded the scrapping table because its purpose is unclear at this point.

select 
    store.Store_Number, store.Store_Name, 
    store.Street_Address, store.City, 
    store.State, store.Zip_Code, 
    store.Zip_Ext, store.Phone_Number, 
    store.Email_Address, store.Location, 
    store.Latitude, store.Longitude,
    store.status, store.SM_Name, 
    store.DM_Nmae, store.RVP_Name, 
    stores.yearopen, store_paper.Newspaper_ID,
    store_paper.Chain, store_paper.Address_Tag,
    store_paper.Ad_Stores, newslist.adsize,
    newslist.primarycontact, newslist.primaryphone,
    newslist.primaryemail, newslist.Newspaper,
    newslist.billing 
    from wwwstores.dbo.Stores stores 
    inner join wwwstores.dbo.zSTORE store
        on stores.Store = store.Store_Number 
    inner join wwwAdvertising.dbo.Store_Paper store_paper
        on store_paper.Store_Number = store.Store_Number 
    inner join wwwAdvertising.dbo.Newslist newslist
        on newslist.? = ?.?
    order by stores.yearopen;

Oleg

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-06-17T15:37:21Z

Kev Riley answered Jun 17, '10

Now I've reformatted the query, it's blinding obvious what's wrong - its amazing how readability can aid query writing.

You reference 2 tables in but without any JOIN clause

wwwAdvertising.dbo.Store_Paper 

wwwAdvertising.dbo.Newslist

but without knowing how those tables relate to the others, it's going to be a guess to fix it

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Jun 17, 2010 at 05:27 PM

I think I was able to decipher the store_paper, but he newslist relationship is still unknown :(

0 ·

question