Re: SQL question - Mailing list pgsql-general

I'm afraid, I'm not used to SQL92 join syntax and almost all my experience
is in Oracle but how about:

SELECT t1.uid
             , t1.xname
             , t2.uid
             , t3.uid
  FROM                         table1  t1
               INNER JOIN table2  t2 ON t1.uid = t2.uid
               INNER JOIN table3  t3 ON t2.uid = t3.uid
 UNION
SELECT t1.uid
              , t1.xname
              , t2.uid
              , NULL
  FROM                         table1  t1
               INNER JOIN table2  t2 ON t1.uid = t2.uid
 WHERE NOT EXISTS
                 (SELECT NULL
                      FROM table3 t3
                   WHERE t3.uid = t1.uid
                 )
 UNION
SELECT t1.uid
              , t1.xname
              , NULL
              , t3.uid
  FROM                          table1 t1
                INNER JOIN table3 t3 ON t1.uid = t3.uid
WHERE NOT EXISTS
                (SELECT NULL
                     FROM table2 t2
                  WHERE t2.uid = t3.uid
                )

Perhaps there was a solution using outer joins and case statements within
the SELECT clause. Perhaps there is also a solution using subselects in the
SELECT clause. However, this is all I can do for tonight.

Vincent

----- Original Message -----
From: "Uwe C. Schroeder" <uwe@oss4u.com>
To: <pgsql-general@postgresql.org>
Sent: Saturday, November 06, 2004 3:13 PM
Subject: [GENERAL] SQL question


-----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-----


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly