Hi all,
Is it possible to create a view (to combine multiple tables logically.....like Oracle) in MySQL. If not, what is the alternative to solve this issue.
The following describes my tables and what I am trying to create a view for.
Create table SE_BOOK(
NAME varchar(100),
PUBLICATION varchar(100),
EDITION int,
ISBN int,
AUTHOR varchar(250),
CALLNO int
);
Create table SE_USER(
NAME varchar(100),
STUDENT_ID varchar(30) NOT NULL,
PASSWORD varchar(10),
ADDRESS varchar(250),
HOME_PHONE varchar(50),
EMAIL varchar(250)
);
Create table SE_BOOK_ISSUE(
STUDENT_ID varchar(30) NOT NULL,
ISBN int,
FIRST_ISSUE_DATE date, #this is used to see if it is more than 2 months since issue
DUE_DATE date #this will be changed for renewal
);
Create table SE_LOGIN(
NAME varchar(100),
USER_ID varchar(30) NOT NULL,
USER_TYPE varchar(50),
PASSWORD varchar(10),
};
create or replace view SE_BOOK_VIEW as
select
h1.STUDENT_ID,
h1.STUDENT_NAME,
h2.ISBN,
h2.BOOK_NAME,
h3.DUE_DATE,
h3.FIRST_ISSUE_DATE,
from SE_USER h1,
SE_BOOK h2,
SE_BOOK_ISSUE h3
where h1.STUDENT_ID = h3.STUDENT_ID
and h2.ISBN = h3.ISBN
This last query doesnt work for me (works in Oracle).
Please comment.
Thanks in advance.
Sudha Visit to know more about me
Is it possible to create a view (to combine multiple tables logically.....like Oracle) in MySQL. If not, what is the alternative to solve this issue.
The following describes my tables and what I am trying to create a view for.
Create table SE_BOOK(
NAME varchar(100),
PUBLICATION varchar(100),
EDITION int,
ISBN int,
AUTHOR varchar(250),
CALLNO int
);
Create table SE_USER(
NAME varchar(100),
STUDENT_ID varchar(30) NOT NULL,
PASSWORD varchar(10),
ADDRESS varchar(250),
HOME_PHONE varchar(50),
EMAIL varchar(250)
);
Create table SE_BOOK_ISSUE(
STUDENT_ID varchar(30) NOT NULL,
ISBN int,
FIRST_ISSUE_DATE date, #this is used to see if it is more than 2 months since issue
DUE_DATE date #this will be changed for renewal
);
Create table SE_LOGIN(
NAME varchar(100),
USER_ID varchar(30) NOT NULL,
USER_TYPE varchar(50),
PASSWORD varchar(10),
};
create or replace view SE_BOOK_VIEW as
select
h1.STUDENT_ID,
h1.STUDENT_NAME,
h2.ISBN,
h2.BOOK_NAME,
h3.DUE_DATE,
h3.FIRST_ISSUE_DATE,
from SE_USER h1,
SE_BOOK h2,
SE_BOOK_ISSUE h3
where h1.STUDENT_ID = h3.STUDENT_ID
and h2.ISBN = h3.ISBN
This last query doesnt work for me (works in Oracle).
Please comment.
Thanks in advance.
Sudha Visit to know more about me