In BioSQL, PhyloDB module, there are tables like this:

<br/>CREATE TABLE tree_qualifier_value<br/>(<br/> tree_id integer NOT NULL, -- The tree with which the metadata is being associated.<br/> term_id integer NOT NULL, -- The name of the metadate element as a term from a controlled vocabulary (or ontology).<br/> "value" text, -- The value of the metadata element.<br/> rank integer NOT NULL DEFAULT 0, -- The index of the metadata value if there is more than one value for the same metadata element. If there is only one value, this may be left at the default of zero.<br/> CONSTRAINT tree_qualifier_value_pkey PRIMARY KEY (tree_id, term_id, rank),<br/> CONSTRAINT fkterm_treequal FOREIGN KEY (term_id)<br/> REFERENCES term (term_id) MATCH SIMPLE<br/> ON UPDATE NO ACTION ON DELETE NO ACTION,<br/> CONSTRAINT fktree_treequal FOREIGN KEY (tree_id)<br/> REFERENCES tree (tree_id) MATCH SIMPLE<br/> ON UPDATE NO ACTION ON DELETE CASCADE<br/>)<br/>

This becomes a problem when I want to retrieve values that are related to one tree. For example, here are some data from the table

tree_id	term_id	value	rank
2	1	"Wagner78"	0
2	2	"Single Tree"	0
2	3	"Wagner78"	0
2	5	"885"	0
2	6	"Ladiges, P. Y.; Humphries, C. J."	0
2	7	"A cladistic study of Arillastrum, Angophora, and Eucalyptus (Myrtaceae). "	0
2	8	"Transformed cladistic; character compatibility; branch and bound, and Farris-Wagner methods ...	0

There is another table term that has term_id, and name. For example, the term (term_id=2) has the name 'dc.title'
I found a few ways to join the data and create a tabular format that each row belongs to one tree_id.

First, I can use what is called self join :

select t1.tree_id, t1.value as dc_title, t2.value as dc_identifier, t3.value as dc_abstract<br/>FROM  tree_qualifier_value t1, tree_qualifier_value t2, tree_qualifier_value t3, term te1, term te2, term te3<br/>where t1.tree_id = t2.tree_id and t2.tree_id = t3.tree_id and t1.term_id = te1.term_id and te1.name='dc.title' and t2.term_id = te2.term_id and te2.name='dc.identifier'<br/>and t3.term_id = te3.term_id and te3.name='dc.abstract'

Here I used different table aliases for the same table, and each alias represents a different instance of the table.

Another way is to use FULL OUTER JOIN:
select * from <br/>(SELECT tree_id, tq.value as dc_identifier FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.identifier') as ide<br/>FULL OUTER JOIN<br/>(SELECT tree_id, tq.value as dc_title FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.title' ) as title<br/>using (tree_id)<br/>FULL OUTER JOIN<br/>(SELECT tree_id, tq.value as dc_abstract FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and te.name='dc.abstract' ) as abstract<br/>USING (tree_id)<br/>

Interestingly, I also tried to use the PostgreSQL XML support to generate a piece of XML from that table. Here instead of using JOIN, I used UNION:

SELECT tree_id, xmlagg(xvalue) as xml FROM (<br/>SELECT tree_id, xmlforest(tq.value as "dc:identifier") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.identifier' <br/>UNION ALL <br/>SELECT tq.tree_id, xmlforest(tq.value as "dc:title") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.title' <br/>UNION ALL <br/>SELECT tq.tree_id, xmlforest(tq.value as "dc:abstract") as xvalue <br/>FROM tree_qualifier_value tq, term te <br/>WHERE tq.term_id = te.term_id and te.name='dc.abstract'<br/>) AS tab GROUP BY tree_id

This will produce some thing like:

<br/>"<dc:identifier>885</dc:identifier><dc:title>A cladistic study of Arillastrum, Angophora, and Eucalyptus (Myrtaceae). </dc:title><dc:abstract>Transformed cladistic; character compatibility; branch and bound, and Farris-Wagner methods ...</dc:abstract>"<br/>

For a table with 5287 different tree_id, and a total of 42320 rows, the three method takes 1080ms, 1136ms, 1338ms (average of three tests). With no surprise that the last method takes longer as it puts the XML together.