Different Schema, Same tablename

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
halifaxmanAsked:
Who is Participating?
 
viralypatelConnect With a Mentor Commented:
What you've written is the shortest query i can think of, if you want columns from all four tables.
0
 
viralypatelCommented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
All Courses

From novice to tech pro — start learning today.