SQL Server Performance

Creating Views

Discussion in 'SQL Server 2008 General DBA Questions' started by rohaandba, Feb 15, 2011.

  1. rohaandba New Member

    When I need to create a ‘View’ from the columns of different tables in a Database, Is it necessary that the tables involving in that view should be related directly or indirectly?That is, should any 2 tables have a common column or an indirect Relationship? Or is it still possible to create a view with independent tables without any kind of relationship between them (tables)?Please clarify
    rohaandba, Feb 15, 2011
    #2

  2. FrankKalis Moderator

    I'm not sure I understand what you're after. Usually you would want a query to return data from one or more table(s) that is somehow related on a common column like SELECT... FROM S JOIN P (ON....).
    However, you can also do something like SELECT ... FROM S, P.... In that case you can "relate" tables that usually aren't related logically by "forcing" them into some kind of relation via a CROSS JOIN. Not sure if this is what you want though.
    Can you explain a little bit more?
    FrankKalis, Feb 15, 2011
    #3

  3. rohaandba New Member

    Actually I will explain in detail.
    I have a data base ‘x’.This database ‘x’ has 20 tables.
     Among these 20 tables there is one ‘Root Table’ with a Primary key and there are 5 other tables with foreign keys referring to this ‘Root Table’ primary key.There are 6 other tables which are independent. i.e. not related to this ‘Root Table’ and the other 5 tables which are in reference with this ‘Root Table’.These 6 tables neither have foreign keys referring to the ‘Root Table’ nor any common columns with Root Table and its 5 link tables.Now I am suppose to create a View which involves the columns from the ‘Root Table’, its 5 link tables and the 6 independent tables.Can any one suggest me how to approach this?
    rohaandba, Feb 16, 2011
    #4

  4. satya Moderator

    As referred you can use CROSS JOIN hint to do so, see books online example:
    USE AdventureWorks2008R2;GOSELECT p.BusinessEntityID, t.Name AS TerritoryFROM Sales.SalesPerson pCROSS JOIN Sales.SalesTerritory tORDER BY p.BusinessEntityID;
    satya, Feb 17, 2011
    #5

  5. Madhivanan Moderator

    If you want to get rows from the non-related tables you can use union all too
    <select query with join>
    union all
    select columns from non-related-table1 where...
    union all
    select columns from non-related-table2 where...
    .
    .
    .
    Madhivanan, Mar 21, 2011
    #6

  6. shab_nyc New Member

    As I understand you want to join for instance 2 tables into a view where no forign key relationship defined between the two tables. I dont think it is possible unless you want to use cross join where each row from table 1 will join every row in table 2. like this query:
    select a.ColumnA1, a.ColumnA2, b.ColumnB1, b.ColumnB2 from Table1 a cross join Table2 b


    shab_nyc, Feb 15, 2011
    #7

Share This Page