join2.sql

application/octet-stream

Download

CREATE TABLE bar2 (id serial not null primary key, name varchar not null);
CREATE TABLE bar3 (id serial not null primary key, name varchar not null);
CREATE TABLE bar4 (id serial not null primary key, name varchar not null);
CREATE TABLE bar5 (id serial not null primary key, name varchar not null);
CREATE TABLE bar6 (id serial not null primary key, name varchar not null);
CREATE TABLE bar7 (id serial not null primary key, name varchar not null);
CREATE TABLE bar8 (id serial not null primary key, name varchar not null);
CREATE TABLE bar9 (id serial not null primary key, name varchar not null);
CREATE TABLE bar10 (id serial not null primary key, name varchar not null);
CREATE TABLE bar11 (id serial not null primary key, name varchar not null);
CREATE TABLE bar12 (id serial not null primary key, name varchar not null);
CREATE TABLE bar13 (id serial not null primary key, name varchar not null);
CREATE TABLE bar14 (id serial not null primary key, name varchar not null);

CREATE TABLE bletch2 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch3 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch4 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch5 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch6 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch7 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch8 (id serial not null primary key, name varchar not null);
CREATE TABLE bletch9 (id serial not null primary key, name varchar not null);

CREATE TABLE bletch1 (
	id serial not null,
	name varchar not null,
	bletch2_id integer not null references bletch2 (id),
	bletch3_id integer references bletch3 (id),
	bletch4_id integer references bletch4 (id),
	bletch5_id integer references bletch5 (id),
	bletch6_id integer references bletch6 (id),
	bletch7_id integer references bletch7 (id),
	bletch8_id integer references bletch8 (id),
	bletch9_id integer references bletch9 (id),
	PRIMARY KEY (id)
);

CREATE TABLE bar1 (
	id serial,
	name varchar not null,
	bletch1_id integer not null references bletch1 (id),
	bar2_id integer not null references bar2 (id),
	bar3_id integer not null references bar3 (id),
	bar4_id integer not null references bar4 (id),
	bar5_id integer not null references bar5 (id),
	bar6_id integer not null references bar6 (id),
	bar7_id integer not null references bar7 (id),
	bar8_id integer references bar8 (id),
	bar9_id integer references bar9 (id),
	bar10_id integer references bar10 (id),
	bar11_id integer references bar11 (id),
	bar12_id integer references bar12 (id),
	bar13_id integer references bar13 (id),
	bar14_id integer references bar14 (id),
	PRIMARY KEY (id)
);

CREATE OR REPLACE VIEW bletch_view AS
SELECT
	bletch1.*, bletch2.name AS bletch2, bletch3.name AS bletch3,
	bletch4.name AS bletch4, bletch5.name AS bletch5, bletch6.name AS bletch6,
	bletch7.name AS bletch7, bletch8.name AS bletch8, bletch9.name AS bletch9
FROM
	bletch2, bletch1
	LEFT JOIN bletch3 ON bletch1.bletch3_id = bletch3.id
	LEFT JOIN bletch4 ON bletch1.bletch4_id = bletch4.id
	LEFT JOIN bletch5 ON bletch1.bletch5_id = bletch5.id
	LEFT JOIN bletch6 ON bletch1.bletch6_id = bletch6.id
	LEFT JOIN bletch7 ON bletch1.bletch7_id = bletch7.id
	LEFT JOIN bletch8 ON bletch1.bletch8_id = bletch8.id
	LEFT JOIN bletch9 ON bletch1.bletch9_id = bletch9.id
WHERE
	bletch1.bletch2_id = bletch2.id;

CREATE OR REPLACE VIEW bar2_view AS
SELECT
	bar1.*, bar2.name AS bar2, bar3.name AS bar3,
	bar4.name AS bar4, bar5.name AS bar5, bar6.name AS bar6,
	bar7.name AS bar7, bar8.name AS bar8,
	bletch_view.name AS bletch1, bletch_view.bletch2, bletch_view.bletch3,
	bletch_view.bletch4, bletch_view.bletch5, bletch_view.bletch6,
	bletch_view.bletch7, bletch_view.bletch8, bletch_view.bletch9
FROM
	bar2, bar3, bar4, bar5, bar6, bar7, bar8, bletch_view, bar1
WHERE
	bar1.bar2_id = bar2.id AND bar1.bar3_id = bar3.id
	AND bar1.bar4_id = bar4.id AND bar1.bar5_id = bar5.id
	AND bar1.bar6_id = bar6.id AND bar1.bar7_id = bar7.id
	AND bar1.bar8_id = bar8.id
	AND bar1.bletch1_id = bletch_view.id;

CREATE OR REPLACE VIEW bar3_view AS
SELECT
	bar1.*, bar2.name AS bar2, bar3.name AS bar3,
	bar4.name AS bar4, bar5.name AS bar5, bar6.name AS bar6,
	bar7.name AS bar7, bar8.name AS bar8,
	bletch_view.name AS bletch1, bletch_view.bletch2, bletch_view.bletch3,
	bletch_view.bletch4, bletch_view.bletch5, bletch_view.bletch6,
	bletch_view.bletch7, bletch_view.bletch8, bletch_view.bletch9
FROM
	bar2, bar3, bar4, bar5, bar6, bar7, bar8, bletch_view, bar1
WHERE
	bar1.bar2_id = bar2.id AND bar1.bar3_id = bar3.id
	AND bar1.bar4_id = bar4.id AND bar1.bar5_id = bar5.id
	AND bar1.bar6_id = bar6.id AND bar1.bar7_id = bar7.id
	AND bar1.bletch1_id = bletch_view.id;