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... ? |
Paul J. Lucas wrote:
> 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... ? > 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' ) ORDER BY v.value Jack Klebanoff |
On Mon, 16 May 2005, Jack Klebanoff wrote:
> Use an ORDER BY clause, e.g. But then all the metadata for a single image isn't grouped together. - Paul |
Paul J. Lucas wrote:
> On Mon, 16 May 2005, Jack Klebanoff wrote: > >> Use an ORDER BY clause, e.g. > > > But then all the metadata for a single image isn't grouped > together. > > - Paul > It sounds like a secondary sort on image id would group all the image metadata together in city order. Not knowing the cardinality between the tables (e.g. One-to-One, One-to-Many, etc.) I can't say what he exact clause should be. You probably need to specify the foreigh key column name of each MANY relationship to insure that related items are grouped. Assuming relationships of One-to-Many between each record set EG: 1 CITY : MANY image_id : MANY key_id : MANY value_id I would recommend: ...ORDER BY city, image_id, key_id, value_id |
Free forum by Nabble | Edit this page |