Help with SELECT and selfjoins

From: Joachim Trinkwitz <jtr_at_uni-bonn.de>
Date: 2000/05/19
Message-ID: <87k8gqu7kb.fsf@waffel.germanistik.uni-bonn.de>#1/1

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