Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Create a View from multiple tables

    Hi,
    I have 3 tables and I'm trying to merge data from all 3 tables into a 2 column view and if anyone can help that would be great. I want the column names from Table B and Table C to be entered into the Field column in Table A and the rows from Table B and Table C to be entered into Table A's Value column.

    Table A: (2 column field and value)

    Details Value
    ID 1
    Employee No 123456
    Leaving Date 01-05-2012
    Leaving Reason 11

    Table B: (2 columns)

    ID Date Notified
    1 01-04-2012

    Table C: (a lookup table for Leaving Reason)

    Id Detail
    1 Early Retirement

    All tables are joined by ID and I want to end up with a View:

    Field Value
    Employee No 12345
    Leaving Date 01-05-2012
    Leaving Reason Early Retirement
    Date Notified 01-04-2012

    Thanks
    Dan

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Where is the issue?
    Simple join and rename result columns might be enough, like...
    FROM A join B on B.id = A.id join C on C.id = A.id
    and
    SELECT ... C.detai AS Leaving Reason, ...


    By the way,
    you wrote "... from all 3 tables into a 2 column view ...".
    But, your sample have 4 columns.
    Field Value
    Employee No 12345
    Leaving Date 01-05-2012
    Leaving Reason Early Retirement
    Date Notified 01-04-2012

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you realised that I used C.id = A.id?

    If you want to relate "Leaving Reason 11" in Table A with "id 1" in Table C,
    you should explain the way to relate them.

    Or, your table design and/or sample data might be wrong.

Posting Permissions

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