Many things in life can be joined. You can join two pieces of wood to construct something useful. You can join a club. You can join with a person of your choosing. Joins can be good things. Sometimes...

Doug K. was working on utilities to manage applications. Among other things, this would entail keeping track of which administrators were responsible for each application. As a first pass, the developers came up with a table like so:

create table applications (
      name        varchar(20) primary key, 
      attribute   varchar(20) not     null, 
      listen_port integer     not     null unique, 
      admin_user1 varchar(20) not     null, 
      admin_user2 varchar(20)
);

Doug suggested that at some point, there might be more than two admins for a given application, and suggested that those fields be moved to another table:

create table application_admins (
      name       varchar(20) references applications,
      admin      varchar(20) not null
);

Since their time was short and the developers were leary of modifying the code that referenced this table, Doug gave them the following query (with comments):

# This is evil. Really, you should do a separate query against the "admins" table 
# to get the list of admins for each application, instead of this very ugly query
$query = q {
    select applications.name, attribute, listen_port, AD1.admin1, AD2.admin2 
      from applications
      left outer join (select name, MIN(admin) as admin1 
                         from application_admins 
                        group by name) as AD1 using name 
      left outer join (select name, MAX(admin) as admin2 
                         from application_admins 
                        group by name) as AD2 using name
};

At some point the developer realized they did, in fact, need more than two admin accounts, and asked the DBA for help to change the application_admins table and query accordingly:

create table application_admins (
      name       varchar(20) references applications,
      admin1     varchar(20) not null, 
      admin2     varchar(20) not null, 
      admin3     varchar(20) not null, 
      admin4     varchar(20) not null, 
      admin5     varchar(20) not null
);

$query = q {
   select applications.name, attribute, listen_port, AD1.admin1, AD2.admin2, AD3.admin3, AD4.admin4, AD5.admin5 
     from applications
     left outer join (select name, MIN(admin1) as admin1 
                        from application_admins 
                       group by name) as AD1 using name 
     left outer join (select name, MAX(admin2) as admin2 
                        from application_admins 
                       group by name) as AD2 using name
     left outer join (select name, MAX(admin3) as admin3 
                        from application_admins 
                       group by name) as AD3 using name 
     left outer join (select name, MAX(admin4) as admin4 
                        from application_admins 
                       group by name) as AD4 using name
     left outer join (select name, MAX(admin5) as admin5 
                        from application_admins 
                       group by name) as AD5 using name
};

While Doug thought his original query was evil, ya gotta love the way they got a DBA to help them make the changes, and still ended up with that table and query.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!