Hello,
I'm redesigning parts of my database to normalize it better and to make
a more general solution available for other parts of the system.
My goal now is to create a view which will let me do a select in the
style of:
SELECT (unit id) (general option value) (color value) (number value)
FROM unit_view;
The SELECT query should return all units, and for each configuration
option it should return either the stored value (if available) or null
if no such option exist for the unit.
With the test data in the database I provide below I would like the
query to return:
unit id general option color number
1, 1, null, 2
2, 1, 1, null
Anyone who knows how to create such view?
Thank you in advance,
/Daniel
-------------------------------
PRAGMA FOREIGN_KEYS=ON;
CREATE TABLE unit_types (
id integer primary key,
name varchar(20) NOT NULL
);
CREATE TABLE units (
id integer primary key,
unit_type_id int not null,
name varchar(20) NOT NULL,
CONSTRAINT "FK_units" FOREIGN KEY( unit_type_id ) REFERENCES
unit_types ( id )
);
CREATE TABLE configtypes(
id integer primary key,
name varchar(20) NOT NULL
);
CREATE TABLE configvalues(
id integer primary key,
name varchar(20) NOT NULL,
value int NOT NULL
);
CREATE TABLE unittype_config(
unit_id int not null,
configtype_id int not null,
configvalue_id int not null,
CONSTRAINT "PK_uc" PRIMARY KEY (unit_id, configtype_id,
configvalue_id),
CONSTRAINT "FK_uc_001" FOREIGN KEY( unit_id ) REFERENCES unit_types
( id ),
CONSTRAINT "FK_uc_002" FOREIGN KEY( configtype_id ) REFERENCES
configtypes ( id ),
CONSTRAINT "FK_uc_003" FOREIGN KEY( configvalue_id ) REFERENCES
configvalues ( id )
);
--create test data
INSERT INTO unit_types VALUES(1, "Number unit");
INSERT INTO unit_types VALUES(2, "Color unit");
INSERT INTO configtypes VALUES(1, "General option");
INSERT INTO configtypes VALUES(2, "Color");
INSERT INTO configtypes VALUES(3, "Number");
INSERT INTO configvalues VALUES(1, "Yes", 1);
INSERT INTO configvalues VALUES(2, "No", 0);
INSERT INTO configvalues VALUES(3, "Blue", 0);
INSERT INTO configvalues VALUES(4, "Red", 1);
INSERT INTO configvalues VALUES(5, "Green", 2);
INSERT INTO configvalues VALUES(6, "1", 1);
INSERT INTO configvalues VALUES(7, "2", 2);
INSERT INTO unittype_config VALUES(1, 1, 1);
INSERT INTO unittype_config VALUES(1, 3, 7);
INSERT INTO unittype_config VALUES(2, 1, 1);
INSERT INTO unittype_config VALUES(2, 2, 4);
INSERT INTO units values(1, 1, "Unit 1");
INSERT INTO units values(2, 2, "Unit 2");
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
