Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Different Schema, Same tablename

Posted on 2011-09-14
3
Medium Priority
?
357 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

824 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