Add new colums to existing view

[bidur]

hi all,

Is it possible to add new column to existing view? For example:
let us have three tables:
table1 with columns(c1,c2 ,c3 )
table2 with columns (cc1,cc2, cc3)
table3 with columns(ccc1,cc c2)

we have a view "View1" with columns say c1, cc1
Now is it possible to add new column ccc2 to the existing view "View1"

thanks

[wizardry]

yes you can just use: alter view select as column names but only if you defined the view.

here is the docs for this.

http://dev.mysql.com/doc/refman/5.0/en/alter-view.html

[bidur]

hi wizardry ,
thanks for your reply.
I went through the link. I m still confused.
It would be easy if you could provide a simple example

thanks

[wizardry]

you can use the alter statement as well where create is and add your new columns; don't forget to include the old ones as well because you have to recreate the existing view, with the new columns instead of what a typical alter statement does.

create view t3 as select a.t1_id, a.t2_id_fk, a.t2_dt, b.t2_id, b.t2_name, b.t2_date from t1 a, t2 b;

ok t3 is your view name, then you want to use alias for your columns you can use a.t1 or table name.coulmn name i.e. t1.t1_id i prefer and suggest using the alias to create a good programming practice. alias is a.t1_id

then in your from statement tablename a

you assign your table name the alias used.


you can create a view of up to 64 table joins in the current version of mysql.

but thats not just on views its table joins period.

what are you using the view for? are you manipulating data in this view?

let me know if this helps!

[bidur]

Hi wizardry,
I am looking if views could be useful or efficient use of joins could solve my problem.
I have got different tables with the following definition:(sim plified for now)
name with(nid,fid,ei d,name)
email with(eid,fid,et ype,email)
family with(fid)
name_email with(nid,eid)

Here, we have email associated with each name. For now I have three different cases to handle

case 1.emails can be current emails shown in the name table i.e. eid field
query:
SELECT name.nid,name.n ame,name.e_id
FROM name

case 2.emails can be personal emails shown in the name_email table i.e. nid-eid pair
query:
SELECT name.nid,name.n ame, name_email.e_id
FROM name
LEFT JOIN name_email ON name.nid=name_e mail.n_id

case 3.emails can be family emails which is linked by the fid
query:
SELECT name.nid,name.n ame,email.email
FROM name
LEFT JOIN family ON name.fid = family.fid
LEFT JOIN email ON family.fid = email.fid

These query work fine if they we select only emails.
But for case 1 and case 2 if we SELECT more fields for e.g. etype then teh result gets mixed with some other unwanted results.
This is because of joins.
In this scenario my query are
case 1.(shows unwanted result mixed with desired result)
query:
SELECT name.nid,name.n ame,email.etype ,name.eid
FROM name
LEFT JOIN family ON name.fid = family.fid
LEFT JOIN email ON family.fid = inf.email.fid

case 2.(shows unwanted result mixed with desired result)
query:
SELECT name.nid,name.n ame,email.etype , name_email.eid
FROM name
LEFT JOIN name_email ON name.id=name_em ail.nid
LEFT JOIN family ON name.fid = family.fid
LEFT JOIN email ON family.fid = email.fid

case3.

query:
SELECT name.nid,name.n ame,email.email ,email.etype
FROM name
LEFT JOIN family ON name.fid = family.fid
LEFT JOIN email ON family.fid = email.fid

[sivashanmugam]

Hi,

if the table anv and bpo has any matching columns with anv then full outer join and insert into mybpo

or else u can insert like this

insert into mybpo ( columnname)
select bpo from anv

and
insert into mybpo ( columnname)
select usr from bpo

[wizardry]

can you post your code for the tables?

reason its pulling the wrong results is because of your querys your wanting to pull data from three tables where their ids = id. a left join is going to return all results for the parent table were child table = clause.

inner join returns where id = id and all columns.

outter join is similar to inner but it is defined by the third table.

right join is the opposite of left join.

paste your code for the tables, so i can look at the structure and ensure your table constraint are fine, and that will allow me to see about your querys in more depth.

one thing you could try is instead of defing your join in your select statements is to use an alias like i mention before, the engine is smart enough to determine what join it needs to run.

i.e.

select a.bid, a.name, b.email, b.type, c.fid, a.fid, b.fid
from
name a,
email b,
family c
where a.fid=b.fid,
and a.fid=c.fid;

[bidur]

Hi wizardry,
thanks for your reply.
the codes for the tables goes as follows:
--
-- Table structure for table `family`
--

CREATE TABLE `family` (
  `fid` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`fid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;# MySQL returned an empty result set (i.e. zero rows).
 
 
-- 
-- Table structure for table `email`
-- 

CREATE TABLE `email` (
  `eid` int(11) NOT NULL auto_increment,
  `fid` int(11) NOT NULL default '0' COMMENT 'Links email addresses for related people',
  `email` varchar(40) NOT NULL default '',
  `etype` enum('Contact','Family','Office','Personal') NOT NULL default 'Personal',
  PRIMARY KEY  (`eid`),
  KEY `email_family` (`fid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=86 ;# MySQL returned an empty result set (i.e. zero rows).

-- 
-- Constraints for table `email`
-- 
ALTER TABLE `email`
  ADD CONSTRAINT `email_family` FOREIGN KEY (`fid`) REFERENCES `family` (`fid`);# MySQL returned an empty result set (i.e. zero rows).


-- 
-- Table structure for table `name`
-- 

CREATE TABLE `name` (
  `nid` int(11) NOT NULL auto_increment,
  `fid` int(11) NOT NULL default '0' COMMENT 'Links related people',
  `eid` int(11) default NULL ,
  `name` varchar(50) NOT NULL default '',
  `gender` enum('Male','Female') NOT NULL default 'Male',
   PRIMARY KEY  (`nid`),
  KEY `name_email` (`eid`), 
  KEY `name_family` (`fid`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;# MySQL returned an empty result set (i.e. zero rows).

-- 
-- Constraints for table `name`
-- 
ALTER TABLE `name`

  ADD CONSTRAINT `name_ibfk_11` FOREIGN KEY (`eid`) REFERENCES `email` (`eid`),
  ADD CONSTRAINT `name_ibfk_8` FOREIGN KEY (`fid`) REFERENCES `family` (`fid`);# MySQL returned an empty result set (i.e. zero rows).
 
-- 
-- Table structure for table `name_email`
-- 

CREATE TABLE `name_email` (
  `nid` int(11) NOT NULL default '0' COMMENT 'Links email addresses for a person',
  `eid` int(11) NOT NULL default '0',
  KEY `name_email_name` (`nid`),
  KEY `name_email_email` (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;# MySQL returned an empty result set (i.e. zero rows).

-- 
-- Constraints for table `name_email`
-- 
ALTER TABLE `name_email`
  ADD CONSTRAINT `name_email_ibfk_1` FOREIGN KEY (`nid`) REFERENCES `name` (`nid`),
  ADD CONSTRAINT `name_email_ibfk_2` FOREIGN KEY (`eid`) REFERENCES `email` (`eid`);# MySQL returned an empty result set (i.e. zero rows).

[wizardry]

ok i see what your problem is its the constraints what your trying to do is a one to one relationship between these tables with family as parent 1 => 1 email, name, name_email i will post the code in a moment. You might need to restructure your database some.

      7 drop table if exists `family`;$
      8  $
      9 CREATE TABLE `family` ( $
     10   `fid` int(11) NOT NULL auto_increment, $
     11   PRIMARY KEY  (`fid`) $
     12 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;# MySQL returned an empty result set (i.e. zero rows). $
     13  $
     14 insert into `family` (`fid`) values ('1');$
     15 insert into `family` (`fid`) values ('2');$
     16 insert into `family` (`fid`) values ('3');$
     17 insert into `family` (`fid`) values ('4');$
     18 insert into `family` (`fid`) values ('5');$
     19 $
     20  $
     21  drop table if exists `email`;$
     22 $
     23 CREATE TABLE `email` ( $
     24   `eid` int(11) NOT NULL auto_increment, $
     25   `fid` int(11) NOT NULL default '0' COMMENT 'Links email addresses for related people', $
     26   `email` varchar(40) NOT NULL default '', $
     27   `etype` enum('Contact','Family','Office','Personal') NOT NULL default 'Personal', $
     28   PRIMARY KEY  (`eid`), $
     29   KEY `email_family` (`fid`),$
     30 foreign key(`fid`)$
     31 references `family`(`fid`)$
     32 on delete cascade$
     33 on update cascade $
     34 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=86 ;# MySQL returned an empty result set (i.e. zero rows). $
     35  $
     36 insert into `email` (`eid`, `fid`, `email`, `etype`) values ('1', '1', 'email1@test.com', 'Personal');$
     37 insert into `email` (`eid`, `fid`, `email`, `etype`) values ('2', '2', 'email2@test.com', 'Personal');$
     38 insert into `email` (`eid`, `fid`, `email`, `etype`) values ('3', '3', 'email3@test.com', 'Personal');$
     39 insert into `email` (`eid`, `fid`, `email`, `etype`) values ('4', '4', 'email4@test.com', 'Personal');$
     40 insert into `email` (`eid`, `fid`, `email`, `etype`) values ('5', '5', 'email5@test.com', 'Personal');$
     41 $
     42 $
     43  $
     44 CREATE TABLE `name` ( $
     45   `nid` int(11) NOT NULL auto_increment,
     46   `fid` int(11) NOT NULL default '0' COMMENT 'Links related people', $
     47   `eid` int(11) default NULL , $
     48   `name` varchar(50) NOT NULL default '', $
     49   `gender` enum('Male','Female') NOT NULL default 'Male', $
     50    PRIMARY KEY  (`nid`), $
     51   KEY `name_email` (`eid`),  $
     52   KEY `name_family` (`fid`),$
     53 foreign key(`fid`)$
     54 references `family`(`fid`)$
     55 on delete cascade$
     56 on update cascade  $
     57 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53 ;# MySQL returned an empty result set (i.e. zero rows). $
     58  $
     59 insert into `name` (`nid`, `fid`, `eid`, `name`, `gender`) values ('1', '1', '1', 'test1', 'male');$
     60 insert into `name` (`nid`, `fid`, `eid`, `name`, `gender`) values ('2', '2', '2', 'test1', 'male');$
     61 insert into `name` (`nid`, `fid`, `eid`, `name`, `gender`) values ('3', '3', '3', 'test1', 'male');$
     62 insert into `name` (`nid`, `fid`, `eid`, `name`, `gender`) values ('4', '4', '4', 'test1', 'male');$
     63 insert into `name` (`nid`, `fid`, `eid`, `name`, `gender`) values ('5', '5', '5', 'test1', 'male');$
     64 $
     65  $
     66   $
     67 CREATE TABLE `name_email` ( $
     68   `ne_id` int(11) NOT NULL auto_increment COMMENT 'primary key', $
     69   `nid_fk` int(11) NOT NULL default '0' COMMENT 'Links email addresses for a person', $
     70   `eid_fk` int(11) NOT NULL default '0', $
     71 primary key (`ne_id`), $
     72  KEY `name_email_name` (`nid_fk`), $
     73   KEY `name_email_email` (`eid_fk`),$
     74 foreign key(`nid_fk`) $
     75 references `name`(`nid`)$
     76 on delete cascade$
     77 on update cascade,$
     78 foreign key(`eid_fk`)$
     79 references `email`(`eid`)$
     80 on delete cascade$
     81 on update cascade $
     82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;# MySQL returned an empty result set (i.e. zero rows). $
     83  $
     84 $
     85 insert into `name_email`(`ne_id`, `nid_fk`, `eid_fk`) values ('1', '1', '1'); $
     86 insert into `name_email`(`ne_id`, `nid_fk`, `eid_fk`) values ('2', '2', '2'); $
     87 insert into `name_email`(`ne_id`, `nid_fk`, `eid_fk`) values ('3', '3', '3'); $
     88 insert into `name_email`(`ne_id`, `nid_fk`, `eid_fk`) values ('4', '4', '4'); $
     89 insert into `name_email`(`ne_id`, `nid_fk`, `eid_fk`) values ('5', '5', '5');
     147 create VIEW `ev` AS select `a`.`email` AS `email`,`a`.`etype` AS `etype`,`b`.`nid` AS `nid`,`b`.`name` AS `name`,`b`.`gender` AS `gender` from (`email` `a` join `name` `b`) where (`a`.`fid` = `b`.`fid`) */;$
    148 $
    149 -- Dump completed on 2009-02-27 22:29:32$

i hope this helps.

from what i was reading in your code you wanted a one on one but with the the name_email table you wanted a one two many.

I don't know how you wanted to use that one two many on name_email unless you have other tables that the join table connects to. but anyhow if you want a one to many on the other tables just rename the constraint foreign key to be a _fk on the end of the name. and modify the constraint id's.

can you explain what exactly you want to do with these tables relationship wise and if they connect to another table for the join table?

[bidur]

Hi wizardry,
thanks a lot for your answers.
As I have explained earlier in the post. Here, we have email associated with each name and have to handle three different cases .

case 1.emails can be current emails for a person shown in the name table i.e. name.eid field

case 2.emails can be personal emails shown in the name_email table i.e.
name_email.nid-name_email.eid pair
For example Tom is a name with nid value 3 , now name_email will have all the nid-eid pairs for nid=3 i.e all the pairs with nid=3 are Tom's personal emails

case 3.emails can be family emails which is linked by the fid
for example.There is a family of Tom with wife Rita. Both will be having same fid but different nid And this third case should select all the emails from email table as per the fid. For this teh name table joins to name_email table and then to email table by tracing the fid in each table.

thank you