Improve company productivity with a Business Account.Sign Up

x
?
Solved

Different Schema, Same tablename

Posted on 2011-09-14
3
Medium Priority
?
363 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
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
viralypatel earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

595 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