db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul J. Lucas" <pauljlu...@mac.com>
Subject Sorting with sub-select
Date Mon, 16 May 2005 15:46:42 GMT
 	Sorry if this is too-general an SQL question, but suppose I
 	have the following tables:

 		CREATE TABLE image (
 		    image_id       INT NOT NULL PRIMARY KEY
 		                   GENERATED ALWAYS AS IDENTITY,
 		    file_name      VARCHAR(64) NOT NULL
 		);

 		CREATE TABLE md_key (
 		    key_id         INT NOT NULL PRIMARY KEY
 		                   GENERATED ALWAYS AS IDENTITY,
 		    image_id       INT NOT NULL REFERENCES image,
 		    tag            VARCHAR(20)
 		);

 		CREATE TABLE md_value (
 		    value_id       INT NOT NULL PRIMARY KEY
 		                   GENERATED ALWAYS AS IDENTITY,
 		    key_id         INT NOT NULL REFERENCES md_key,
 		    value          VARCHAR(255) NOT NULL
 		);

 	That is a set of images; for each image, a set of keys; for each
 	key, a set of values.  For an image, its set of keys and values
 	comprise its metadata.  Every image can have a different set of
 	keys and values.

 	If I want to select all the metadata for a set of images that
 	actually have a metadata key of 'City', I can use a sub-select
 	like:

 		SELECT i.file_name, k.tag, v.value
 		  FROM image i, md_key k, md_value v
 		 WHERE i.image_id = k.image_id
 		   AND k.key_id = v.key_id
 		   AND i.image_id IN
 			(
 			SELECT i.image_id
 			  FROM image i, md_key k, md_value v
 			 WHERE i.image_id = k.image_id
 			   AND k.key_id = v.key_id
 			   AND k.tag = 'City'
 			)

 	My question is: is there any way to sort the results by the
 	value of City (v.value)?

 	- Paul

 	P.S.: Is there a better place to post general SQL questions?
 	comp.databases... ?

Mime
View raw message