Post reply

SELECT * from 2 tables into a view

  • September 25, 2006 at 7:02 pm

    #115743

    Is there a way to create a view using SELECT * from 2 tables (actually in my case it's from 2 other views, but that shouldn't matter). I want to do the following:

    CREATE VIEW COMBINE_VIEWS AS

    SELECT * FROM VIEW1

    INNER JOIN VIEW2 ON VIEW1.ID_FIELD = VIEW2.ID_FIELD

    The problem is that the field that links the views (ID_FIELD) is in both views so I get this error "Column names in each view or function must be unique."

    I know that if I specify each column name then I can just select the ID field from one of the views rather than both (or rename the field), but each view consists of many fields so I don't want to do it this way. Also, I will be adding fields to both views over time and want the combined view to include the new fields automatically. BTW, I have it split into 2 views intially because both views have one table in common (hence the common ID field) but the rest of the data is from different tables. It also works well for my users to have the 2 views separate and also to have them combined into 1 view.

    The only other solution I could come up with is to change the name of the ID field in one of the original views so I won't have duplicate names, but I don't like this solution either (I know, I'm picky!). I don't want to do it this way since my users use the ID fields to link to other data (in ArcMap and other applications) and it would cause problems to have the ID field named differently in one of the views.

    Is there some simple, elegant solution to this problem that I have overlooked? I realize that I may be trying to do the impossible. Any comments or suggestions would be appreciated.

    THANKS!

  • September 25, 2006 at 7:25 pm

    #662394

    When you create a view, an entry is made in the syscolumns table for each column in the view.  This is true even if you write the view with SELECT * FROM.

    When you attempt to query columns from the view, the syscolumns table is used at parse time to determine if your query is valid.  If you add a column to a base table after you created a view using SELECT * FROM, it will not be included in the view's syscolumns definition, and will not be part of the view.

    -- Create a table
    CREATE
     TABLE dbo.SampleTable
    (
      Col1 
    int NOT NULL,
      Col2 
    int NOT NULL
    )
    GO
     
    -- Create a view on the table
    CREATE
     VIEW dbo.SampleView
    AS
      SELECT * FROM dbo.SampleTable
    GO
     
    -- Table's and view's entries in syscolumns. Note two columns for each.
    SELECT
     OBJECT_NAME(id), * FROM syscolumns 
     WHERE id IN (OBJECT_ID('dbo.SampleView'), OBJECT_ID('dbo.SampleTable'))
     ORDER BY OBJECT_NAME(id)
    GO
     
    -- Add a column to the table
    ALTER
     TABLE dbo.SampleTable ADD Col3 int NOT NULL
    GO
     
    -- Table's entries in syscolumns: now has 3 columns.
    -- View's entries in syscolumns: still has 2 columns.
    SELECT
     OBJECT_NAME(id), * FROM syscolumns 
     WHERE id IN (OBJECT_ID('dbo.SampleView'), OBJECT_ID('dbo.SampleTable'))
     ORDER BY OBJECT_NAME(id)
    GO
     
    -- New column not part of view, even though it looks like it should be.
    -- Select * FROM view will only return 2 columns:
    SELECT
     * FROM dbo.    SampleTable
    SELECT
     * FROM dbo.    SampleViewGO
     
    -- Because new column not part of view, any specfic query for the new table columns 
    -- not in the query will throw an error:
    SELECT
     Col3 FROM dbo.    SampleTable
    GO
    SELECT
     Col3 FROM dbo.    SampleViewGO
    -- Clean up.
    DROP
     VIEW dbo.SampleView
    DROP
     TABLE SampleTable
    GO

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • September 26, 2006 at 2:32 am

    #662427

    hi

    just give a alias-name(s) for the column(ID_FIELD) in the select statement of the view.

    by the way why do you need to select ID_FIELD twice since u r joining on the ID_FIELD.

     

    "Keep Trying"

  • September 26, 2006 at 3:52 pm

    #662593

    Thank you, Eddie. I didn't realize that SELECT * is not dynamic, and that is very good to know. It seems like it would be fairly easy to run an ALTER VIEW statement so that the added fields would show up in the view (I just have to remember to do it). That seems easier to me than having to manually write in all of the names of the added fields.

    I guess what I really want to know is if there is a way to use SELECT * in a view that joins two tables, when the field that links the tables has the same name in both tables. Since the only duplicate field is the join field, and I really only want to have that field show up once in the result set, is there some way to do that?

    I know some will say that using SELECT * is bad style and I should write out all of the field names anyway, but I work in a research environment where things are always changing. I am just trying to reduce the amount of work needed in response to changes in the way my users want to view the data.

  • September 26, 2006 at 8:23 pm

    #662617

    > Since the only duplicate field is the join field, and I really only want to have that field show up once in the result set, is there some way to do that?

    SELECT * grabs all columns from all tables (and views and table functions) in the query.  There isn't a way to block duplicate columns. 

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • September 27, 2006 at 1:31 am

    #662649

    Hi,

    SELECT * really is bad style, but there are places where it can help. I'm using it in one place only - we have some views that are basically reports, and I need to mail them automatically every day. The procedure that sends the mail finds addresses, does SELECT * FROM view ORDER BY column and mails the result. This way, when I'm asked to change a report, I can only change the view and don't have to do anything with the procedure. The view itself does NOT use *, all columns are named explicitly.

    Using * has some limitations. It means ALL columns, and you can not exclude any. It also means you can't assign column names (aliases). Both these conditions together => all columns in the source tables/views must have unique names.

    I wouldn't use SELECT * inside a view. It is better to name all columns you want to display and assign appropriate aliases, if necessary. I can understand why you wanted to do that, but IMHO it isn't a good solution - and in your case, when joining two tables (or views) that both have column with the same name, you simply can't use *, no matter how much that would help you...

    What about the underlying views (view1, view2)? Do you use * there as well? If not, you could change the ID_FIELD name in one of these views with an alias - that would allow using * in your main view, but the same column would appear twice in it, with two different names. As I said, I don't recommend it... and as I just realized when reading your original post again, you don't want to do it (which is correct :-))

  • September 27, 2006 at 6:43 am

    #662692

    IMHO, views of views are a bit like writing functions that call functions... they sure seem useful until you need to troubleshoot...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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