pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Uwe C. Schroeder <uwe@oss4u.com>
To: pgsql-general@postgresql.org
Subject: SQL question
Date: Sat, 6 Nov 2004 15:13:22 -0800
Message-ID: <200411061513.25914.uwe@oss4u.com> (raw)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Here's a question for the SQL guru's out there, which I've been trying to 
solve for the last couple of hours. There's got to be a solution to this, but 
somehow I can't find it.

Tables:

table1 (
	uid int PK,
        uname varchar(64)
)

table2 (
	uid int FK to table1,
        xuid int FK to table 1
)

table3 (
  	uid int FK to table1,
	yuid int FK to table1
)

There might be more tables of the type like table2 and table3, but I'd already 
be happy to solve the puzzle with the 3 tables above.
Ok, assume table1 is the master table - in my case a table used for login 
authentication (some columns removed above)
table2 and table3 are tables where the uid always references to the uid in 
table1. The second "uid" (xuid and yuid in this example) references to 
another uid record in table1. The problem is that there may or may not be 
entries in table2 (or table3) referencing a specific uid in their second uid 
field.
Maybe some data:

table1:
1 test1
2 test2
3 test3

table2:
1 2
1 3
3 1

table3:
1 2
2 3
3 2

What I want to do in a view is the following resultset:

uid  uname xuid yuid
 1    test1     2      2
 1    test1     3
 2    test2             3
 3    test3      1
 3    test3             2


So basically I want to know which uid is connected to which uid, one 
relationship per row. So xuid and yuid shall be identical if records exist in 
both table2 and table3 or the value shall be NULL if a corresponding record 
can't be found in either table2 or table3.

Can anyone here help me out? 

Thanks a lot

	UC

- --
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq
XFIvkCIJHyz7TvvV/XxL4Lk=
=/vxG
-----END PGP SIGNATURE-----




view thread (71+ messages)  latest in thread

Message-ID: <200411061513.25914.uwe@oss4u.com>
Permalink:  ../200411061513.25914.uwe@oss4u.com/
Also on:    postgresql.org/message-id/200411061513.25914.uwe@oss4u.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: uwe@oss4u.com
  Subject: Re: SQL question
  In-Reply-To: <200411061513.25914.uwe@oss4u.com>
  References: <200411061513.25914.uwe@oss4u.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox