Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    8

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    8
    Quote Originally Posted by r937 View Post
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Posts
    8
    Quote Originally Posted by PUK_999 View Post
    I needed to ... and disambiguate the PHONE table from the PHONE column.
    Hmm - this wasn't necessary after all!

  6. #6
    Join Date
    Dec 2009
    Posts
    8
    Quote Originally Posted by r937 View Post
    yes, the subqueries for phone_type_id are needed here
    OK, thank you.

    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

  7. #7
    Join Date
    Dec 2009
    Posts
    8
    Quote Originally Posted by PUK_999 View Post
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2009
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •