Solved

Different Schema, Same tablename

Posted on 2011-09-14
3
348 Views
Last Modified: 2012-05-12
I am quering a SQL 2008 Database

The database has the following tables which I need to query

Mark.Progress
Will.Progress
RG.Progress
CG.Progress

i.e. different schema name, but same table name

I need to get the same field from alll tables

So far I have done the following


select m.matterid,mp.[date],wp.[date],rp.[date],cp.[date],
from matter m inner join Mark.Progress mp on m.matterid = mp.matterid
inner join Will.Progress wp on m.matterid = wp.matterid
inner join RG.Progress rp on m.matterid = rp.matterid
inner join CG.Progress cp on m.matterid = cp.matterid

Is there a better way than this?

Thanks
0
Comment
Question by:halifaxman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
viralypatel earned 500 total points
ID: 36534869
What you've written is the shortest query i can think of, if you want columns from all four tables.
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 36534876
If you are using the same query multiple times, you could probably create a view and then use

select * from View_Name

Open in new window

0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36536948
Will the same "MatterId" value exist in all tables?  If not, it might make more sense to use a UNION or swap the INNER JOIN out with a LEFT JOIN.  This way you don't lose potential data.
0

Featured Post

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question