Professional Web Applications Themes

MS SQL Views with cross join - Microsoft SQL / MS SQL Server

Perhaps you really want UNION ALL instead of CROSS JOIN Consider something like: CREATE VIEW global_account AS SELECT * FROM COMPANY1_TST.dbo.account UNION ALL SELECT * FROM COMPANY2_TST.dbo.account In that case, you might even use partitions, see BOL for more info. If you really wanted a CROSS JOIN, the syntax would be more like: CREATE VIEW global_account AS SELECT * FROM COMPANY1_TST.dbo.account CROSS JOIN SELECT * FROM COMPANY2_TST.dbo.account Bye, Delbert Glass "C Roberts" <junkmailstayconnectedanywhere.com> wrote in message news:796615d.0307081122.38c59423posting.google.co m... > I have two databases that have identical tables (two companies in an > accounting system). Ex. DatabaseA, DatabaseB > > What ...

  1. #1

    Default Re: MS SQL Views with cross join

    Perhaps you really want UNION ALL instead of CROSS JOIN

    Consider something like:
    CREATE VIEW global_account AS
    SELECT * FROM COMPANY1_TST.dbo.account
    UNION ALL
    SELECT * FROM COMPANY2_TST.dbo.account

    In that case, you might even use partitions, see BOL for more info.

    If you really wanted a CROSS JOIN, the syntax would be more like:
    CREATE VIEW global_account AS
    SELECT * FROM COMPANY1_TST.dbo.account
    CROSS JOIN
    SELECT * FROM COMPANY2_TST.dbo.account


    Bye,
    Delbert Glass

    "C Roberts" <junkmailstayconnectedanywhere.com> wrote in message
    news:796615d.0307081122.38c59423posting.google.co m...
    > I have two databases that have identical tables (two companies in an
    > accounting system). Ex. DatabaseA, DatabaseB
    >
    > What I want to do is create a third database (DatabaseC) that
    > essentially has only views that relate to the other two. Furthermore
    > however, I also need to essentially do a cross join so that
    > DatabaseA.TableA appends to DatabaseB.TableA for the purpose of the
    > view. Thus, DatabaseC would be a collection of views that merge
    > identical tables from Databases A and B.
    >
    >
    > I've figured out how to fully qualify references to the other
    > databases (see below). However, any attempt to specify multiple
    > tables, or to use the cross join syntax fails..... any ideas???
    >
    >
    >
    > CREATE VIEW global_account AS
    > SELECT * FROM COMPANY1_TST.dbo.account
    >
    > ** What I need is something like:
    >
    > CREATE VIEW global_account AS
    > SELECT * FROM COMPANY1_TST.dbo.account cross join
    > COMPANY2_TST.dbo.account
    >
    > ** OR **
    >
    > CREATE VIEW global_account AS
    > SELECT * FROM COMPANY1_TST.dbo.account, COMPANY2_TST.dbo.account

    Delbert Glass Guest

  2. #2

    Default Re: MS SQL Views with cross join


    "Delbert Glass" <delbertnoincoming.com> wrote in message
    news:eJPKlNZRDHA.1556TK2MSFTNGP10.phx.gbl...
    > If you really wanted a CROSS JOIN, the syntax would be more like:
    > CREATE VIEW global_account AS
    > SELECT * FROM COMPANY1_TST.dbo.account
    > CROSS JOIN
    > SELECT * FROM COMPANY2_TST.dbo.account
    Sorry, that's wrong.


    Try something like this:

    CREATE VIEW global_account AS
    SELECT
    t1.Col1 as t1Col1,
    t1.Col2 as t1Col2,
    t2.Col1 as t2Col1,
    t2.Col2 as t2Col2
    FROM
    COMPANY1_TST.dbo.account t1
    CROSS JOIN
    COMPANY2_TST.dbo.account t2

    IE you sometimes have to use aliases so that the exposed names are not the
    same.

    Bye,
    Delbert Glass



    Delbert Glass Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. views in 8.0
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 11th, 01:26 PM
  3. 2 views
    By duff@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: May 28th, 06:03 PM
  4. Views
    By Guy Brown in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 08:47 PM
  5. Replies: 4
    Last Post: July 8th, 07:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139