question

Dave Myers avatar image
Dave Myers asked

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-2005selectjoinsview
1 comment
10 |1200

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

TimothyAWiseman avatar image TimothyAWiseman commented ·
Before we try to parse a query like that, could you tell us how it is failing?
0 Likes 0 ·
dvroman avatar image
dvroman answered

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

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

Oleg avatar image
Oleg answered

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

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

Kev Riley avatar image
Kev Riley answered

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 comment
10 |1200

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

Oleg avatar image Oleg commented ·
I think I was able to decipher the store_paper, but he newslist relationship is still unknown :(
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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