Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997

Outer Joins for Reporting Data Stored in Different Rows

Question:
[Joe Celko's Phone Book Puzzle]

You are trying to set up an office telephone directory and have the following tables:

    CREATE TABLE Employees
        (empid INTEGER PRIMARY KEY,
        firstname CHAR(20) NOT NULL,
        lastname CHAR(20) NOT NULL);

    INSERT INTO Employees VALUES (01, 'John', 'Smith');
    INSERT INTO Employees VALUES (02, 'Fred', 'Jones');
    INSERT INTO Employees VALUES (03, 'Bill', 'Black');
    INSERT INTO Employees VALUES (04, 'Joe', 'Celko');

    CREATE TABLE Phones
        (empid INTEGER NOT NULL,
        phonetype CHAR(3) NOT NULL CHECK phonetype IN ('hom', 'fax'),
        phonenumber CHAR(12) NOT NULL,
        PRIMARY KEY (empid, phonetype, phonenumber),
        FOREIGN KEY empid REFERENCES Employees.empid );

    INSERT INTO Phones VALUES (01, 'hom', '213-555-0101');
    INSERT INTO Phones VALUES (01, 'fax', '213-555-0101');
    INSERT INTO Phones VALUES (02, 'hom', '213-555-0202');
    INSERT INTO Phones VALUES (03, 'fax', '213-555-0303');
The codes 'hom' and 'fax' tell you if the number is their home phone number or if it is their FAX number. What you want to print out is a report with one line per employee, giving both numbers and showing a NULL if either or both are missing.

It is worth noting that the FOREIGN KEY constraint on the Phones table means that you cannot list a telephone number for someone who is not an employee. The PRIMARY KEY looks a bit large, until you stop and think about all the cases. Employees could share the same FAX or home telephones and a single line could be both voice and FAX services.

Answer:
There are a lot of bad starts to this query. The first thought is to construct the home telephone information as a query in its own right. Since we want to see all the employees, we need an outer join:

    CREATE VIEW Hphones (lastname, firstname, empid, homenumber)
        AS SELECT E1.lastname, E1.firstname, 
            E1.empid, H1.phonenumber
        FROM (Employees AS E1 LEFT OUTER JOIN Phones AS H1
            ON (E1.empid = H1.empid AND H1.phonetype = 'hom'));
Likewise, we could construct the FAX telephone information as a query the same way:
    CREATE VIEW Fphones (lastname, firstname, empid, faxnumber)
        AS SELECT E1.lastname, E1.empid, F1.phonenumber
        FROM (Employees AS E1 LEFT OUTER JOIN Phones AS F1
            ON (E1.empid = F1.empid AND F1.phonetype = 'fax'));
It would seem reasonable to combine these two VIEWs and get the results.
    SELECT Hphones.lastname, 
            Hphones.firstname, 
            homenumber, faxnumber
        FROM Hphones AS H1, Fphones AS F1
        WHERE H1.empid = F1.empid;
But this does not work because it leaves out the "FAX only" people. If we want to preserve both tables, then we need a FULL OUTER JOIN, which might look like this:
    SELECT H1.lastname, H1.firstname, homenumber, faxnumber
        FROM (Hphones AS H1 FULL OUTER JOIN Fphones AS F1
            ON H1.empid = F1.empid);
But this still does not print the names of the "FAX only" people — they show up as NULLs since we are only printing the Hphones people names. The COALESCE() function will take care of that problem for us, like this:
    SELECT COALESCE (H1.lastname, F1.lastname), 
            COALESCE (H1.firstname, F1.firstname), 
            homenumber, faxnumber
        FROM (Hphones AS H1 FULL OUTER JOIN Fphones AS F1
            ON H1.empid = F1.empid);
The bad news is that this will work. It will also run like glue since it will probably materialize the VIEWs before using them.

The real trick is to go back and see that the Fphones and Hphones VIEWs are outer joined to the Employees table. The Employees table can be factored out and the two FROM clauses combined to give:

    SELECT E1.lastname, E1.firstname,
            H1.phonenumber AS Home, 
            F1.phonenumber AS FAX
        FROM ((Employees AS E1 LEFT OUTER JOIN Phones AS H1
            ON (E1.empid = H1.empid AND H1.phonetype = 'hom'))
        LEFT OUTER JOIN Phones AS F1
            ON (E1.empid = F1.empid AND F1.phonetype = 'fax'));
Since this gets all the tables at once, it should run a good bit faster than the false start.

Puzzle provided courtesy of:
Joe Celko
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date