...

View Full Version : How to create view from multiple tables



nani_nisha06
01-21-2013, 11:59 AM
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

BubikolRamios
01-21-2013, 12:47 PM
Assuming that A and W (forinstace) are key columns



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.

nani_nisha06
01-21-2013, 06:20 PM
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......

guelphdad
01-21-2013, 06:33 PM
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.

nani_nisha06
01-22-2013, 07:21 AM
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

nani_nisha06
01-22-2013, 07:52 AM
Below statement also created an view but now transferring any row in them.




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

BubikolRamios
01-22-2013, 08:30 AM
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:



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.

nani_nisha06
01-22-2013, 08:55 AM
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:



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

BubikolRamios
01-22-2013, 01:36 PM
View is like a stored query , so it will happen.

Old Pedant
01-22-2013, 07:51 PM
I think Bubikol is overthinking this.

I *THINK* what you need is pretty simple:


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.

nani_nisha06
01-23-2013, 07:01 AM
I think Bubikol is overthinking this.

I *THINK* what you need is pretty simple:


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....



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum