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

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.

nani_nisha06 · 01-21-2013, 06:20 PM

Quote:

Originally Posted by BubikolRamios

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

Quote:

Originally Posted by guelphdad

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.

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

BubikolRamios · 01-22-2013, 08:30 AM

Quote:

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.

nani_nisha06 · 01-22-2013, 08:55 AM

Quote:

Originally Posted by BubikolRamios

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

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:

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.

nani_nisha06 · 01-23-2013, 07:01 AM

Quote:

Originally Posted by Old Pedant

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