Post reply

Joining a table and a view

  • July 12, 2005 at 3:15 pm

    #88106

    Let me start off by saying I know you cannot join a table and a view or at least I don't think you can.  Hence my question.

    I have 2 databases running on one instance of SQL Server 2000.  I have only granted read permissions to one of the databases as it serves as a "master" database for information on products, colors, sku's, etc.  I need information out of a table in this database to use in another database to be accessed thru a web app.

    The table I need information from, the "master", has approx 300,000 rows of which I need about 30,000.  I want to make sure I get any changes made to the master database - so copying the table isn't an option.  I created a view which executes fine, but now I'm trying to resolve a many to many relationship using the information from the view.

    Any suggestions on how to solve this?  I was thinking of just referencing the master table for join purposes, but I hate to do that.  I'd feel much more comfortable going thru a view.  Also, I could write a custom SQL function that would execute on inserts into the intermediary table I'm using to solve the M to N problem, but it seems like a more elegant solution should exist.

    Any suggestions you have would be greatly appreciated.

  • July 12, 2005 at 3:18 pm

    #573686

    You can join a view and a table. You can also select from another database using 3 parts name >>

    Select A.col1, B.col2 from dbo.TableA A inner join MasterDb.dbo.ViewName B on A.id = B.id

  • July 12, 2005 at 3:38 pm

    #573692

    May I ask why do you say that you can't join a table to a view? obviously from the above post you see an example on how to do it. Are you getting any error?

     


    * Noel

  • July 12, 2005 at 4:17 pm

    #573695

    I hadn't tried to execute code that would join a few and a table.

    I was laying out the table structure in Visio and it said joining a view and a table wasn't valid.  I assumed (and you know what happens when you do that) that joining a table and a view wasn't "proper". 

    Thanks for letting me know it is possible.  That will greatly reduce coding efforts.

    Thanks again.

  • July 12, 2005 at 5:36 pm

    #573712

    If you need help with the code, post the table and view definition (with keys) and some sample data so we can cook it up for you.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply