SQL query construction question (selecting particular records from child table)

PUK_999 · #1 (**permalink**) 01-08-10, 10:02

Hello,

I did have a PERSON table with three different phone numbers on, two of which were NULLable e.g.:

Code:

CREATE TABLE PERSON
(
    PERSON_ID        INT NOT NULL AUTO_INCREMENT,
    PERSON_NAME   VARCHAR(50) NOT NULL,
    PHONE              VARCHAR(20) NOT NULL,
    PHONE_ALT       VARCHAR(20) NOT NULL,
    FAX                 VARCHAR(20) NOT NULL,
    PRIMARY KEY(PERSON_ID)
);

Selecting was easy from this because I could just do SELECT * FROM PERSON within my application code and if PHONE_ALT or FAX were NULL then I'd just set to null in my runtime object.

I've decided to normalise it more to the following three tables:

Code:

/*
* The type of a phone (e.g. PHONE, PHONE_ALT, FAX)
*/
CREATE TABLE PHONE_TYPE
(
    PHONE_TYPE_ID          INT NOT NULL AUTO_INCREMENT,
    PHONE_TYPE_CODE     VARCHAR(20) NOT NULL,
    PRIMARY KEY(PHONE_TYPE_ID),
    UNIQUE KEY PHONE_TYPE_CODE (`PHONE_TYPE_CODE`)
);

CREATE TABLE PERSON
(
    PERSON_ID          INT NOT NULL AUTO_INCREMENT,
    PERSON_NAME     VARCHAR(50) NOT NULL,
    PRIMARY KEY(PERSON_ID)
);

/*
* The person phone numbers.
*/
CREATE TABLE PERSON_PHONE_NUMBER
(
    PERSON_ID             INT NOT NULL,
    PHONE_TYPE_ID      INT NOT NULL,
    PHONE_NUMBER       VARCHAR(20) NOT NULL,
    PRIMARY KEY(PERSON_ID,PHONE_TYPE_ID),
    FOREIGN KEY(PERSON_ID) REFERENCES PERSON(PERSON_ID),
    FOREIGN KEY(PHONE_TYPE_ID) REFERENCES PHONE_TYPE(PHONE_TYPE_ID)
);

/* Types of phone numbers */
INSERT INTO PHONE_TYPE (PHONE_TYPE_CODE) VALUES('PHONE');
INSERT INTO PHONE_TYPE (PHONE_TYPE_CODE) VALUES('PHONE_ALT');
INSERT INTO PHONE_TYPE (PHONE_TYPE_CODE) VALUES('FAX');

My Person application object is still purposely restricted for the 3 phone number types.

I am looking for an SQL statement that'll give me a single row result set with PHONE, PHONE_ALT, and FAX columns, just as-if I did a SELECT * FROM PERSON with the old schema.

Hope someone can help.

Thanks,

PUK

r937 · #2 (**permalink**) 01-08-10, 10:38

Code:

SELECT person.person_id 
     , person.person_name  
     , phone.phone_number AS phone
     , phone_alt.phone_number AS phone_alt
     , fax.phone_number AS fax
INNER
  JOIN person_phone_number AS phone
    ON phone.person_id = person.person_id
   AND phone.phone_type_id = 'PHONE'
LEFT OUTER
  JOIN person_phone_number AS phone_alt
    ON phone_alt.person_id = person.person_id
   AND phone_alt.phone_type_id = 'PHONE_ALT'
LEFT OUTER
  JOIN person_phone_number AS fax
    ON fax.person_id = person.person_id
   AND fax.phone_type_id = 'FAX'

PUK_999 · #3 (**permalink**) 01-08-10, 11:03

Quote:

Originally Posted by r937

Code:

SELECT person.person_id 
     , person.person_name  
     , phone.phone_number AS phone
     , phone_alt.phone_number AS phone_alt
     , fax.phone_number AS fax
INNER
  JOIN person_phone_number AS phone
    ON phone.person_id = person.person_id
   AND phone.phone_type_id = 'PHONE'
LEFT OUTER
  JOIN person_phone_number AS phone_alt
    ON phone_alt.person_id = person.person_id
   AND phone_alt.phone_type_id = 'PHONE_ALT'
LEFT OUTER
  JOIN person_phone_number AS fax
    ON fax.person_id = person.person_id
   AND fax.phone_type_id = 'FAX'

That got me on the right track, thank you!

I needed to include a FROM PERSON clause in the SELECT, and disambiguate the PHONE table from the PHONE column.

I have also ended up with 3 inner SELECT statements to get the PHONE_TYPE_ID from the PHONE_TYPE_CODE e.g.:

Code:

SELECT person.*
     , tphone.phone_number AS phone
     , phone_alt.phone_number AS phone_alt
     , fax.phone_number AS fax
FROM person
INNER
  JOIN person_phone_number AS tphone
    ON tphone.person_id = person.person_id
   AND tphone.phone_type_id = (SELECT PHONE_TYPE_ID FROM PHONE_TYPE WHERE PHONE_TYPE_CODE='PHONE')
LEFT OUTER
  JOIN person_phone_number AS phone_alt
    ON phone_alt.person_id = person.person_id
   AND phone_alt.phone_type_id = (SELECT PHONE_TYPE_ID FROM PHONE_TYPE WHERE PHONE_TYPE_CODE='PHONE_ALT')
LEFT OUTER
  JOIN person_phone_number AS fax
    ON fax.person_id = person.person_id
   AND fax.phone_type_id = (SELECT PHONE_TYPE_ID FROM PHONE_TYPE WHERE PHONE_TYPE_CODE='FAX');

Are inner SELECTs ok here in your opinion? I guess it could be done with more joins but thinking in SQL hurts my brain at the moment!

r937 · #4 (**permalink**) 01-08-10, 11:15

yes, sorry for the sloppiness on my part, i made a couple of very obvious errors and you managed to overcome them quite well

yes, the subqueries for phone_type_id are needed here

however, they would ~not~ be needed if you would only consider changing the design of your tables as follows:

Code:

CREATE TABLE phone_type
( phone_type_code VARCHAR(20) NOT NULL PRIMARY KEY
);
/* Types of phone numbers */
INSERT INTO phone_type (phone_type_code) VALUES ('PHONE');
INSERT INTO phone_type (phone_type_code) VALUES ('PHONE_ALT');
INSERT INTO phone_type (phone_type_code) VALUES ('FAX');
  
CREATE TABLE person_phone_number                                   
( person_id    INTEGER NOT NULL                           
, phone_type_code   VARCHAR(20) NOT NULL                           
, phone_number  VARCHAR(20) NOT NULL                   
, PRIMARY KEY (person_id,phone_type_code)
, FOREIGN KEY (person_id) REFERENCES PERSON (person_id)
, FOREIGN KEY (phone_type_code) REFERENCES PHONE_TYPE (phone_type_code)
);

i find the use of an id for phone_type quite distasteful and mainly useless

PUK_999 · #5 (**permalink**) 01-08-10, 11:17

Quote:

Originally Posted by PUK_999

I needed to ... and disambiguate the PHONE table from the PHONE column.

Hmm - this wasn't necessary after all!

PUK_999 · #6 (**permalink**) 01-08-10, 11:26

Quote:

Originally Posted by r937

yes, the subqueries for phone_type_id are needed here

OK, thank you.

Quote:

however, they would ~not~ be needed if you would only consider changing the design of your tables as follows:

<removed suggested table design>

i find the use of an id for phone_type quite distasteful and mainly useless

Being an application programmer, I've not done much SQL but a project I worked on a few years ago alongside IBM exposed me to some database design. They were using surrogate ids even in the presence of natural keys a lot and I just couldn't understand the additional indirection but was told by someone more senior than me that it was good. The example we were talking about back then might've been a better example than my PHONE_TYPE_ID construction.

I suppose I could hang extra attributes against the phone type in the future, but apart from that it doesn't appear to give me much.

Something I'm on the fence about at the moment. Is it a topic database designers are divided over and debate, or would my design be considered overly complex to most designers?

Thanks,

PUK

PUK_999 · #7 (**permalink**) 01-08-10, 11:37

Quote:

Originally Posted by PUK_999

I suppose I could hang extra attributes against the phone type in the future, but apart from that it doesn't appear to give me much.

I'm sorry - your proposal gives me exactly that too of course! I'm really slow today

r937 · #8 (**permalink**) 01-08-10, 12:06

yes, the use of surrogate ids is quite contentious among database developers

the ones you should watch out for are guys who say you should ~always~ use a surrogate id -- they're idiots

me, personally, i insist on using only natural keys during the logical design stage of development

without understanding the ~natural~ relationships in your data, you're designing blind

once you hand off your validated logical design to the DBAs, they can then insert whatever surrogate keys they wish...

PUK_999 · #9 (**permalink**) 01-08-10, 12:15

I've just been reading this Wikipedia entry on surrogate keys which was quite interesting.

I think I'll take your advice on board. Those keys are additional complexity which'll slow me down in all phases of my project and I can't provide any justification for them.

Thanks again for all your help.

PUK

Internet Services

Web Development

Digital Marketing

Consumer Tech