Daniel Polski wrote:
> I'm redesigning parts of my database to normalize it better [...]
> 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.
SQLite does not have pivot functions; it is not possible to generate
columns automatically from data values.
In this query, the three configuration options are hardcoded:
SELECT units.id AS "unit id",
cv1.value AS "General option",
cv2.value AS "Color",
cv3.value AS "Number"
FROM units
JOIN unit_types ON units.unit_type_id = unit_types.id
LEFT JOIN (unittype_config
JOIN configvalues
ON unittype_config.configvalue_id = configvalues.id
AND unittype_config.configtype_id = 1) AS cv1
ON unit_types.id = cv1.unit_id
LEFT JOIN (unittype_config
JOIN configvalues
ON unittype_config.configvalue_id = configvalues.id
AND unittype_config.configtype_id = 2) AS cv2
ON unit_types.id = cv2.unit_id
LEFT JOIN (unittype_config
JOIN configvalues
ON unittype_config.configvalue_id = configvalues.id
AND unittype_config.configtype_id = 3) AS cv3
ON unit_types.id = cv3.unit_id
Are you sure that the normalization hasn't gone a *little* bit too far?
> FOREIGN KEY( unit_id ) REFERENCES unit_types ( id )
Why is this column not called unit_type_id?
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
