Solved

Different Schema, Same tablename

Posted on 2011-09-14
3
337 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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sum of items in two tables not equal. 5 46
Getting "Invalid Operation" Error when opening a Recordset 10 33
SQL Instance service gone? 5 36
SQL- GROUP BY 4 22
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

763 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