SQL/XML Multi table join question
От | Mina R Waheeb |
---|---|
Тема | SQL/XML Multi table join question |
Дата | |
Msg-id | 275890790804161134k2921148dy60206d890a047d46@mail.gmail.com обсуждение исходный текст |
Список | pgsql-sql |
Hi all, I have the following tables (parent and two children) CREATE SEQUENCE person_seq; CREATE TABLE person(_id integer DEFAULT nextval('person_seq') NOT NULL,_timestamp TIMESTAMP NOT NULL,_lastModified TIMESTAMPNOT NULL,name VARCHAR(255) NOT NULL,age INTEGER DEFAULT NULL,PRIMARY KEY (_id) ); CREATE TABLE person_nationality(_id serial NOT NULL,_parent INTEGER NOT NULL,nationality VARCHAR(255) NOT NULL,FOREIGN KEY(_parent) REFERENCES person (_id) ON DELETE CASCADE,PRIMARY KEY (_id) ); CREATE TABLE person_variables(_id serial NOT NULL,_parent INTEGER NOT NULL,variable VARCHAR(255) DEFAULT NULL,value VARCHAR(255)DEFAULT NULL,FOREIGN KEY (_parent) REFERENCES person (_id) ON DELETE CASCADE,PRIMARY KEY (_id) ); I'm trying to generate XML element for each person which also contains the person nationality and variables in one result set SELECT XMLROOT ( XMLELEMENT ( NAME information, XMLATTRIBUTES ( person._id AS pid ),XMLAGG( XMLELEMENT( name "nationality", person_nationality.nationality ) ),XMLAGG( XMLELEMENT( name "value", person_variables.value ) ) ), VERSION '1.0', STANDALONE YES ) FROM person INNER JOIN person_nationality ON person_nationality._parent = person._id INNER JOIN person_variables ON person_variables._parent = person._id GROUP BY person._id LIMIT 100 OFFSET 10000; The above query return number of variables * nationality for each person which is expected (for me) because of the join logic. Also i can't use DISTINCT keyword in XMLAGG function. Any idea how to do this? or is there is any aggregate function returns array from row set, i didn't find any in the documenation. Thanks in advance Mina.
В списке pgsql-sql по дате отправления: