Outer Joins for Reporting Data Stored in Different Rows

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
[email protected]

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as