From: | A Gilmore <agilmore(at)shaw(dot)ca> |
---|---|
To: | Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Querying many to many relations |
Date: | 2007-02-10 19:28:51 |
Message-ID: | 45CE1CF3.1020509@shaw.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I need to run a query that retrieves all rows in a table and passes off
the information to another app, it involves a many-to-many relation,
that I'm handling with a subselect. Obviously this is costly, I was
wondering if anyone has better way of approaching this problem that
would allow me to avoid the subselect.
Here is an example of the problem:
-- SQL BEGIN
BEGIN;
CREATE TABLE links (
id INTEGER PRIMARY KEY,
name TEXT,
ahref TEXT
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE links_categories (
lid integer REFERENCES links,
cid integer REFERENCES categories,
PRIMARY KEY (lid, cid)
);
INSERT INTO links (id,name,ahref) VALUES (1,'postgresql',
'http://www.postgresql.org');
INSERT INTO links (id,name,ahref) VALUES (2,'bbc', 'http://www.bcc.com');
INSERT INTO categories (id,name) VALUES (3,'informative');
INSERT INTO categories (id,name) VALUES (4,'news');
INSERT INTO categories (id,name) VALUES (5,'technology');
INSERT INTO categories (id,name) VALUES (6,'database');
INSERT INTO links_categories (lid,cid) VALUES (1,3);
INSERT INTO links_categories (lid,cid) VALUES (1,5);
INSERT INTO links_categories (lid,cid) VALUES (1,6);
INSERT INTO links_categories (lid,cid) VALUES (2,3);
INSERT INTO links_categories (lid,cid) VALUES (2,4);
SELECT id,name,ahref,
array_to_string(array(SELECT links_categories.cid FROM
links_categories WHERE links_categories.lid = id), ':') AS categories
FROM links;
-- SQL END
The problem is my app needs to create an XML entry for each link row
that contains all applicable category IDs. Can I create a similar query
while avoiding the subselect?
I could do a JOIN/SORT approach and have the result processed by PHP
into a single XML entry per link row, but that would be more messy and
maybe not even any faster.
Thank you for your time,
- Adrien
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2007-02-11 00:41:56 | Re: Querying many to many relations |
Previous Message | Richard Broersma Jr | 2007-02-09 23:01:38 | Re: postgres.conf |