[SQL] joining two simular (but not identical tables)
I have two similar but not identical tables. I would like to create a view that combines the contents of both tables into a single view, where each record in each table is visible as a separate record in the view. table a Column | Type | Modifiers ---+---+--- prcode| character varying(12) | descr | character varying(55) | rrp | numeric(10,5) | sugcusmkdn| numeric(6,2) | customerpr| numeric(7,2) | costpr| numeric(11,6) | lengths | character(1) | profitpercent | numeric(6,2) | table b Column | Type | Modifiers ---+---+--- prcode| character varying(12) | descr | character varying(55) | rrp | numeric(10,5) | customerpr| numeric(7,2) | supdis| numeric(6,3) | costpr| numeric(11,6) | lengths | character(1) | profitpercent | numeric(6,2) | dnprice | numeric(7,2) | stcode| character varying(18) | dnprofit | numeric(5,2) | Thanks for any help. Regards Neil. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] joining two simular (but not identical tables)
On Sun, Sep 04, 2005 at 10:19:12PM +1000, Neil Dugan wrote: > I have two similar but not identical tables. > I would like to create a view that combines the contents of both tables > into a single view, where each record in each table is visible as a > separate record in the view. Sounds like you're looking for UNION. http://www.postgresql.org/docs/8.0/static/queries-union.html http://www.postgresql.org/docs/8.0/static/typeconv-union-case.html Does the following example do what you want? CREATE TABLE a (x integer, y integer); INSERT INTO a (x, y) VALUES (1, 2); INSERT INTO a (x, y) VALUES (3, 4); CREATE TABLE b (x integer, z integer); INSERT INTO b (x, z) VALUES (5, 6); INSERT INTO b (x, z) VALUES (7, 8); CREATE VIEW v AS SELECT x, y, NULL AS z FROM a UNION ALL SELECT x, NULL AS y, z FROM b; SELECT * FROM v; x | y | z ---+---+--- 1 | 2 | 3 | 4 | 5 | | 6 7 | | 8 (4 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help with UNION query
Hi all, I have the followin query: SELECT g.id from onp_group g where g.groupname IN (SELECT ug.groupname from onp_user_group ug WHERE ug.username = 'andreak') UNION (SELECT child_id FROM onp_group_children WHERE group_id IN (SELECT g.id from onp_group g where g.groupname IN (SELECT ug.groupname from onp_user_group ug WHERE ug.username = 'andreak'))); Any ideas on how I can simplyfy this query so that I only need to provide ug.username='andreak' once? I would much rather prefer a query which doesn't involve UNION or SUB-SELECTS. Here are the table-definitions: CREATE TABLE onp_group( id integer PRIMARY KEY REFERENCES onp_entity(id) on delete cascade, p_id integer REFERENCES onp_group(id) on delete cascade, groupname varchar NOT NULL unique ); CREATE TABLE onp_group_children( group_id integer NOT NULL REFERENCES onp_group(id), child_id integer NOT NULL REFERENCES onp_group(id), UNIQUE(group_id, child_id) ); CREATE TABLE onp_user_group( groupname varchar not null REFERENCES onp_group(groupname) ON DELETE CASCADE ON UPDATE CASCADE, username varchar not null REFERENCES onp_user(username) ON DELETE CASCADE ON UPDATE CASCADE, primary key(groupname, username) ); -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpES6WS7Vazt.pgp Description: PGP signature
Re: [SQL] Help with UNION query
Replying to my self... Here's a somewhat simpler version without the IN-queries: SELECT g.id from onp_group g, onp_user_group ug where g.groupname = ug.groupname AND ug.username = 'andreak' UNION SELECT gc.child_id FROM onp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = 'andreak'; But I would very much appreciate if someone manages to rewrite this query so that it doesn't specify ug.username='anderak' twice. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpQwOXgyYLkC.pgp Description: PGP signature
Re: [SQL] Help with UNION query
On Sunday 04 September 2005 20:08, Andreas Joseph Krogh wrote: > Replying to my self... Again... I managed to "reduse" my query to the following: SELECT gr.groupname FROM onp_group gr, onp_user u WHERE gr.id IN ( SELECT g.id FROM onp_group g, onp_user_group ug WHERE g.groupname = ug.groupname AND ug.username = u.username UNION SELECT gc.child_id FROM onp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = u.username ) AND u.username = 'andreak'; But "EXPLAIN ANALYZE" tells me it's about 8x slower than this one: SELECT gr.groupname FROM onp_group gr WHERE gr.id IN( SELECT g.id from onp_group g, onp_user_group ug where g.groupname = ug.groupname AND ug.username = 'andreak' UNION SELECT gc.child_id FROM onp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = 'andreak' ); So, the question stands: any idea on how to rewrite the lower wuery to only specify "ug.username='andreak'" once? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpqjjn2DsTzg.pgp Description: PGP signature
