SQL question

**Uwe C. Schroeder** · Nov 23 '05, 02:49 AM

Re: SQL question

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

If someone knows this it would be great - because I'm still curious how to
solve it. However I just remodelled my db structure to eliminate the problem
(basically I pulled the several tables into one since each of the
table2/table3 tables only has 3 fields)
so now I do:
table2 (
uid int FK to table1,
luid int FK to table1,
is_in_table3 boolean,
is_in_table4 boolean,
.....
)

this just needs a simple select with a join against table1.
UC

On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote:[color=blue]
> 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[/color]

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

iD8DBQFBjXebjqG XBvRToM4RAq+kAK CyK1bwfRBdVoAvo i0boELr90wSmwCd HUEf
p9L9Z1OSHwqvYn+ ZnDWSTQw=
=Ih7b
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Vincent Hikida** · Nov 23 '05, 02:49 AM

Re: SQL question

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.co m>
To: <pgsql-general@postgre sql.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)

iD8DBQFBjVqVjqG XBvRToM4RAhk5AK CjjGc4VilA45PnP ZoKluTNYUP6FACg rRQq
XFIvkCIJHyz7Tvv V/XxL4Lk=
=/vxG
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Greg Stark** · Nov 23 '05, 02:50 AM

Re: SQL question

"Uwe C. Schroeder" <uwe@oss4u.co m> writes:
[color=blue]
> 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
> )[/color]

SELECT *
FROM table2
FULL OUTER JOIN table3 ON (table2.uid = tabletable3.uid AND xuid = yuid)
JOIN table1 USING (uid)

Or if you find it clearer

SELECT *
FROM table1
JOIN (table2 FULL OUTER JOIN table 3 ON (table2.uid = tabletable3.uid AND xuid = yuid)) USING (uid)

You might want to have an outer join (RIGHT OUTER JOIN in the first form, LEFT
OUTER JOIN in the second) if you want to list records from table1 for which
there are no matching records in table2 or table3.

This is going to be a hard query to get to be fast though.

The other alternative would be to do a self-join of table1 to table1 and then
use subqueries to check for matching table2 or table3 entries. This would be a
lose if the relationships are relatively sparse, but if you have more tables
it might end up being a win, I don't know.

Something like

SELECT table1.*,
(select xuid from table2 where uid = child.uid) as xuid,
(select yuid from table3 where uid = child.uid) as yuid,
(select zuid from table4 where uid = child.uid) as zuid,
...
FROM table1
CROSS JOIN table1 AS child

--
greg

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**Uwe C. Schroeder** · Nov 23 '05, 02:50 AM

Re: SQL question

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

Thanks for your answers Greg & Vincent.
Although I solved the problem by a change of schema - I'm happy that I have
something to digest I didn't know before. One never learns enough ...

U.C.

On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote:[color=blue]
> 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[/color]

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

iD8DBQFBjz6bjqG XBvRToM4RApNRAJ 9tJzn/3DHSYEZPlGSjzU0 H/FsQIwCffw4N
XJuHiF0al0pzInv Ob3BP1Jg=
=490X
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

SQL question

SQL question

Comment

Comment

Comment

Comment