I thought I'd post the SQL we used to solve a problem at work. Since we had to solve this problem in Postgresql 7.0.3, we didn't have outer joins to work with, so we used unions. I personally find unions hard to type but easy to read, so I can forgive them they're long winded ways.
The issue we had to solve was how to show executives and they're administrative assistants. So, we created two new fields in the database, a bool that says whether they are an executive or not, and a text to hold the employee number of the admin assistant.
That's just a simple inner self equi-join like so:
$table="phonebook";
$query = "select t1.lname||', '||t1.pname as full_name, t1.pos as title, t1.ext, t1.ms, t2.lname||', '||t2.pname as ad_name, t2.ext, t2.ms as ad_ms, t2.pos as ad_title from $table t1, $table t2 where t1.is_exec='T' and t1.admin_enum=t2.enum";
While we are operating on one table, we have to use an alias to do a self join. t1 is the table alias holding the executives, and t2 is the table alias holding the assistants.
But there's a problem. This query only works for executives who have admin assistants. Some don't and still need to be in the phone book. So, we union the above query with one in which we select all executives who don't have their executive flag set.
$query.="UNION (select lname||', '||pname as full_name, pos, ext, ms, '', '', '', '' from phonebook where is_exec='T' and admin_enum = '')";
But now we have another problem. What if an executive loses his admin assistant. Neither of the above queries can handle that, since the employee number of the assistant won't be valid anymore, their record won't return on the first query, and since they have something in the admin_enum field, they won't be in the second set either.
The quick answer may seem like another union join, but in fact, we can use 'not in' syntax to make this act more like an outer join.
We change the end of the query to this:
... and admin_enum not in (select enum from $table))";
and we're done.
The issue we had to solve was how to show executives and they're administrative assistants. So, we created two new fields in the database, a bool that says whether they are an executive or not, and a text to hold the employee number of the admin assistant.
That's just a simple inner self equi-join like so:
$table="phonebook";
$query = "select t1.lname||', '||t1.pname as full_name, t1.pos as title, t1.ext, t1.ms, t2.lname||', '||t2.pname as ad_name, t2.ext, t2.ms as ad_ms, t2.pos as ad_title from $table t1, $table t2 where t1.is_exec='T' and t1.admin_enum=t2.enum";
While we are operating on one table, we have to use an alias to do a self join. t1 is the table alias holding the executives, and t2 is the table alias holding the assistants.
But there's a problem. This query only works for executives who have admin assistants. Some don't and still need to be in the phone book. So, we union the above query with one in which we select all executives who don't have their executive flag set.
$query.="UNION (select lname||', '||pname as full_name, pos, ext, ms, '', '', '', '' from phonebook where is_exec='T' and admin_enum = '')";
But now we have another problem. What if an executive loses his admin assistant. Neither of the above queries can handle that, since the employee number of the assistant won't be valid anymore, their record won't return on the first query, and since they have something in the admin_enum field, they won't be in the second set either.
The quick answer may seem like another union join, but in fact, we can use 'not in' syntax to make this act more like an outer join.
We change the end of the query to this:
... and admin_enum not in (select enum from $table))";
and we're done.