Re: Help with SELECT and selfjoins

From: Peter Connolly <peter_at_wpi.edu>
Date: 2000/05/19
Message-ID: <Pine.OSF.4.21.0005191137340.32190-100000@wpi.WPI.EDU>#1/1

You didn't send your code, but I think you may have overlooked outer joins. It looks like you want to bring back a null for an off no or priv no if it does not exist. Therefore you should do something like:

SELECT

       u.fname,
       u.lname,
       off.phone off no,  
       priv.phone priv no
  FROM
       username u,
       phone number off,
       phone number priv

 WHERE u.id = off.id (+)
   AND u.id = priv.id (+)
   AND off.type = 'off'
   AND priv.type = 'priv'

Or you could consider office phone and private phone as multivalued attributes and throw them in your username table (I beleive that breaks 4NF).

-Peter

On 19 May 2000, Joachim Trinkwitz wrote:

> Hi,
>
> shame upon me, I stumbled over a presumably basic SQL question. I
> hope, someone is overbearing enough to answer me.
>
> I have a database with usernames and phone numbers. As peop e can (but
> mustn't) have both office and private phone numbers, I considered best
> to go the way of normalization and put the names + an ID number in one
> table, the pone numbers + a number type definition + the corresponding
> ID (referencing to the first table) in another, like this:
>
> ID | f name | l name phone | type | ID
> -------------------- -----------------
> 1 | Jane | Smith 123 | off | 1
> 2 | John | Doe 789 | priv | 1
> 345 | off | 2
>
> My simple, silly problem is how to get out the data with a SELECT in
> one row, like this:
>
> f name | l name | off no | priv no
> ----------------------------------
> John | Doe | 123 |
> Jane | Smith | 345 | 789
>
> I know I can join two tables and tried several ways, but either I got
> redundant rows or no one at all.
>
> All hints/answers appreciated, even RTFM.
> Greetings,
> joachim
>
Received on Fri May 19 2000 - 00:00:00 CDT