InterBase: How to create view with a multible tables?

Question

I am using SQL statement to create a view, but all the time I have problems with syntax.

The four tables I have are:

CREATE TABLE "BOOK_ORDER" 
(
  "ID"  INTEGER NOT NULL,
  "Open_Date"   DATE NOT NULL,
  "Close_Date"  DATE,
  "Student_ID"  INTEGER NOT NULL,
  "Book_ID" INTEGER NOT NULL,
CONSTRAINT "PK_ORDER_ID" PRIMARY KEY ("ID")
);
ALTER TABLE "BOOK_ORDER" ADD CONSTRAINT "FK_ORDER_BOOK" FOREIGN KEY ("Book_ID") REFERENCES BOOK ("ID") ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "BOOK_ORDER" ADD CONSTRAINT "FK_ORDER_STUDENT" FOREIGN KEY ("Student_ID") REFERENCES STUDENTS ("STUDENT_ID") ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE "STUDENTS" 
(
  "STUDENT_ID"  INTEGER NOT NULL,
  "STUDENT_NAME"    VARCHAR(50) NOT NULL,
  "TICKET"  INTEGER NOT NULL,
  "GROUP_ID"    INTEGER NOT NULL,
CONSTRAINT "PK_StUDENTS" PRIMARY KEY ("STUDENT_ID")
);
ALTER TABLE "STUDENTS" ADD CONSTRAINT "FK_GROUP" FOREIGN KEY ("GROUP_ID")     REFERENCES GROUPS ("GROUP_ID") ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE "GROUPS" 
(
  "GROUP_ID"    INTEGER NOT NULL,
  "TITLE"   VARCHAR(50) NOT NULL,
  "CURATOR" VARCHAR(50) NOT NULL,
  "CURATOR_TEL" VARCHAR(20) NOT NULL,
  "FAC_ID"  INTEGER NOT NULL,
CONSTRAINT "PK_GROUP" PRIMARY KEY ("GROUP_ID")
);

CREATE TABLE "BOOK" 
(
  "ID"  INTEGER NOT NULL,
  "CODE"    INTEGER NOT NULL,
  "Name"    VARCHAR(50) NOT NULL,
  "Year"    DATE NOT NULL,
  "Publishing_Office"   VARCHAR(50) NOT NULL,
  "Language"    INTEGER NOT NULL,
CONSTRAINT "PK_BOOK_ID" PRIMARY KEY ("ID")
);

I need to show info about order: OpenDate, CloseDate, Student, StudentGroup, Book.

@SoulTrain I just start learning SQL. I try many examples, but I cant understand how to do this correctly. — KAMAEL, Jan 27 '15 at 21:04
I selected what I thought was the important fields, but if you need the rest, just replace the fields in the select portion with an *. — , Jan 27 '15 at 22:16

score 1 · Accepted Answer · answered Jan 27 '15 at 22:15

You create a view with the syntax of CREATE VIEW view_name AS SELECT statement where statement is your regular SELECT statement.

Therefore, you would do this:

CREATE VIEW view_name AS
SELECT BOOK_ORDER.id, BOOK_ORDER.OpenDate, BOOK_ORDER.CloseDate, students.student_name, groups.title, book.name
FROM book_order, students, groups, book
WHERE book_order.student_id = students.student_id AND
      book_order.book_id = book.id AND
      students.group_id = groups.group_id

The where clause is performing a simple join by including values repeated across tables once. I cannot guarantee this will work, but it is a good start.

Also, to get data from the view, select from it like a regular table with the view as the table name.

InterBase: How to create view with a multible tables?

1 Answers1