[KLUG Members] Advanced SQL Questions (Smart Views)

[Adam Williams]

I have two related tables -

create table code_master (
  code_set_id   serial primary key,
  code_length   int not null,
  code_length_strict char(1) 
   check (code_length_strict == 'Y' 
           or code_length_strict == 'N'), 
  code_description char(50) not null);

create table code_detail (
  code_set_id  int
    references code_master(code_set_id)
      on delete cascade,
  record_id serial,
  code_abbrev char(8) not null,
  code_text char(120) not null,
   primary key (code_set_id, record_id));

Since we have web applications that use a myriad of various appreviations 
(D&B, SIC, ITA, blah blah....) this lets me avoid having a myriad of 
little two column tables,  and the code_master table is used by some class 
libraries to be a little smarter about what a valid code is.

For the sake of user sanity in creating queries/reports I have defined 
views such as -

create view county_code(county_code, county_desc) AS
  select code_abbrev, code_text
    from code_detail, code_master
  where code_detail.code_set_id = code_master.code_set_id
   and code_description = 'County Code';

This works great, probably not the absolutely most effiecient way but 
Informix IDS blows through this stuff in the blink of an eye irregardless.

So views are cool!

But I'm wondering if anyone knows any SQL92/99 tricks to create a view 
between two records that MAY have a relation.

Such that (for example) I have two tables

equipment(equip_id, make, model, serial)
rental_equipment(equip_id, rental_asset, rented_yn)

I'd like to create a view that relates equipment and rental_equipment, but 
not every record in equipment will have a corresponding record in 
rental_equipment.

If I just do 

create veiw equipment_detailed
  (equip_id, make, model, serial, rental_asset, rented_yn)
 AS select equipment.equip_id, make, model, serial,
           rental_asset, rented_yn
     from equipment, rental_equipment
    where equipment.equip_id = rental_equipment.equip_id

I won't get any records from equipment that don't correspond to a record 
in rental_equipment (obviously).  What I'd like is to just get those 
records fields form the rental_equipment table back null.

I remember some fancy kinds of joins in my classes,  but can't recall if 
any of them pertain to this.

Basically, such views would remove a WHOLE LOT of lines of code from the 
class library.  And I'd bet Informix could do it faster, if it can do it 
at all.