Post reply

Combining two tables into one view and joining columns

  • October 28, 2010 at 11:51 am

    #240318

    I have two similar tables: Purchases (idnum, trndate, amount) and Payments (idnum, trndate, amount). I would like to create a view that combines them into one with purchases being a negative number and payments a positive number but in the same column. Basically creating a new view by inserting both tables into the view and have it be something like: Transactions (idnum, trndate, amount).

  • October 28, 2010 at 12:06 pm

    #1243763

    CREATE VIEW Transactions

    AS

    SELECT idnum,

    trndate,

    amount * -1 as amount

    FROMPurchases

    UNION ALL

    SELECT idnum,

    trndate,

    amount

    FROMPayments

    GO

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 28, 2010 at 12:58 pm

    #1243790

    Thank you, this worked perfectly.

  • October 28, 2010 at 1:25 pm

    #1243803

    CREATE VIEW Transactions

    AS

    SELECT A.idnum,

    A.trndate,

    A.C.amount * -1 as amount,

    B.idnum,

    B.trndate,

    B.amount

    FROM Purchases A inner join Payments B on A.idnum= B.idnum.

    GO

    We can use in this way also if A.idnum=B.Idnum ,Correct me if I'm wrong.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • October 28, 2010 at 1:37 pm

    #1243809

    That would work if the 2 tables were in fact related by idnum=idnum, but the OP did not say the tables were related.....just similar.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

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