Results 1 to 4 of 4

Thread: Creating a View

  1. #1
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15

    Unanswered: Creating a View

    I have 2 tables. T1 is for current data. T2 is a audit tracking table for T1. There will be several records in T2 for each 1 in T1. T2 has a Action Field that stores the last action and a auditID to record changes on T1.

    What I want to do is create a view that shows the current records in T1 and all the records in the audit tabel T2. I can do the Join but this would duplicate all the fields.

    I am looking for something like this:

    Select 'AuditID' AuditID,TD.*,'Action' Action from TrakrDetails TD
    --Union
    Select TDA.* from TrakrDetails_Audit TDA
    order by AuditID desc

    This craps out because there are 2 additional Fields in T2.

    Any Suggestions?

    Thanks
    Jon
    Hope is the feeling that the feeling that you have will not last very long.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry but you have to list out all of the columns...

    If you want to show columns that aren't in the other table you can use a literal like space, or you can use a null

    SELECT ' ' AS Col1,
    , Null As Col2
    , Col3 FROM myTable99
    UNION ALL
    SELECT Col1
    , Col2
    , Col3
    FROM myTable00
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15
    Thanks Brett

    Your way works.

    Normally I would have done it this way but it seemed like it was the long way around (thats the way it normally goes for me).

    I thought there might be a easy way I was missing.

    Thanks Again
    Jon
    Hope is the feeling that the feeling that you have will not last very long.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    As an aside NEVER use SELECT *

    (Except for analysis, never for code...save yourself a lot of pain)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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