Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts

    How to create view from multiple tables

    Hi Friends,

    I would like to know how we can create one view from 2 different tables.

    Please help me with the commands.

    For example:

    Table apple has A,B,C,D cloumns with the data & table Orange as W,X,Y,Z.

    making view apple_orange with A,B,C, X,Y,Z.

    Please help me

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Assuming that A and W (forinstace) are key columns

    Code:
    create view apple_orange as
    select * from apple a left join orange o on a.A = o.W
    Otherwise, can't be done.

    In general you did not tell how that view would be something smart, and I cant see it either.
    Last edited by BubikolRamios; 01-21-2013 at 12:50 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by BubikolRamios View Post

    In general you did not tell how that view would be something smart, and I cant see it either.
    BubikolRamios,

    Smart in the senses ??

    I am looking for a view which just collect some elements from table 1 & some from table 2 making a view, is what i am looking for.

    of course i am noob yet and learning myself so these little things might be not smart for you but it is tuff for me

    Anyhow thanks for support......

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    To use two tables you'd have to use a JOIN or a UNION. You have given us fake columns so all we can do is guess. Giving clear examples would actually get you a better answer.

  • #5
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by guelphdad View Post
    To use two tables you'd have to use a JOIN or a UNION. You have given us fake columns so all we can do is guess. Giving clear examples would actually get you a better answer.
    guelphdad,

    I am glad that I have created a view with the JOIN but I dont see the data is moved but only column is created.

    what I am trying to achieve is.

    Example:I have 2 tables category & purchase


    cate_id cate_descrip
    CA001 Science
    CA002 Technology
    CA003 Computers
    CA004 Nature
    CA005 Medical



    aut_id aut_name country home_city
    AUT001 William Norton UK Cambridge
    AUT002 William Maugham Canada Toronto
    AUT003 William Anthony UK Leeds
    AUT004 S.B.Swaminathan India Bangalore
    AUT005 Thomas Morgan Germany Arnsberg


    Now I want create view which as only below columns in it

    cate_id cate_descrip aut_id aut_name
    CA001 Science AUT001 William Norton
    CA002 Technology AUT002 William Maugham
    CA003 Computers AUT003 William Anthony
    CA004 Nature AUT004 S.B.Swaminathan
    CA005 Medical AUT005 Thomas Morgan
    Last edited by nani_nisha06; 01-22-2013 at 07:32 AM.

  • #6
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Below statement also created an view but now transferring any row in them.


    Code:
    CREATE VIEW view_purchase AS SELECT A.aut_id,A.aut_name,B.cate_id,B.cate_descrip FROM purchase A, category B;
    As above I have already 5 rows in them but i don't see any data in the view.

    Please help me understand what i am doing as an mistake..

    Thanks...
    Nani
    Last edited by nani_nisha06; 01-22-2013 at 07:56 AM.

  • #7
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    CA001 Science AUT001 William Norton
    What does this say ?
    Wiliam bought something from category science.
    That I was talking about. What did he bought ?

    You need two new tables ITEMS and SALES to make any use of all that.

    And the one you now call purchase, rename it to buyer or something.

    EDIT. In case you persist at what you hawe and to clarify making view:

    1. add column to your current purchase table, name it as cate_id
    fill that with same data as you have in category table.

    Then:

    Code:
    create foo as
    select * from category c left join purchase p on c.cate_id = p.cate_id
    any you will get eyactly what you want.
    Last edited by BubikolRamios; 01-22-2013 at 08:41 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #8
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by BubikolRamios View Post
    What does this say ?
    Wiliam bought something from category science.
    That I was talking about. What did he bought ?

    You need two new tables ITEMS and SALES to make any use of all that.

    And the one you now call purchase, rename it to buyer or something.

    EDIT. In case you persist at what you hawe and to clarify making view:

    1. add column to your current purchase table, name it as cate_id
    fill that with same data as you have in category table.

    Then:

    Code:
    create foo as
    select * from category c left join purchase p on c.cate_id = p.cate_id
    any you will get eyactly what you want.
    BubikolRamios,

    okay I will do it and it should solve my purpose but I want to know one thing will view be auto sync from its default table ?

    I dont believe if that can happen? pls clarify.

    Regards,
    nani

  • #9
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    View is like a stored query , so it will happen.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    I think Bubikol is overthinking this.

    I *THINK* what you need is pretty simple:
    Code:
    CREATE VIEW anyNameYouWant
    AS
    SELECT * FROM category, purchase
    WHERE SUBSTRING(category.cate_id,3) = SUBSTRING(purchase.aut_id,4)
    That will match up CA001 with AUT001, as well as CA073 with AUT073, etc.

    That is, it will ignore the "CA" and "AUT" and match up on the numbers only.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    nani_nisha06 (01-23-2013)

  • #11
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    165
    Thanks
    38
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    I think Bubikol is overthinking this.

    I *THINK* what you need is pretty simple:
    Code:
    CREATE VIEW anyNameYouWant
    AS
    SELECT * FROM category, purchase
    WHERE SUBSTRING(category.cate_id,3) = SUBSTRING(purchase.aut_id,4)
    That will match up CA001 with AUT001, as well as CA073 with AUT073, etc.

    That is, it will ignore the "CA" and "AUT" and match up on the numbers only.
    Old pendent,

    Let me check if these can fulfill my requirement thanks....


  •  

    Posting Permissions

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