[KLUG Members] Advanced SQL Questions (Smart Views)

[Buist Justin]

<bonk>  Totally forgot about OUTER Join's.  Quick heads-up for you though, PostgreSQL didn't support the OUTER join until the 7.x series.  If you go with a recent copy though they work just fine.

Justin Buist


-----Original Message-----
From: Adam Williams [mailto:adam@morrison-ind.com]
Sent: Friday, May 03, 2002 4:37 PM
To: members@kalamazoolinux.org
Subject: RE: [KLUG Members] Advanced SQL Questions (Smart Views)


> 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
> [ end snip ]
> perhaps your WHERE clause should read:
> where equipment.eqip_id = rental_equipment.equip_id
>    OR equipment.equip_id NOT IN (select equip_id from rental_equipment)
> .. Probably going to be slower than pooh, but it might not be too bad.

That works, but is indeed really slow, and the queries get real 
complicated.

I phoned up an SQL guru who owed me a back scratch an learned the 
following trick:

select customer_number, company_code, customer_name, txe_percent, 
mk_customer
  from mtacust, OUTER tax_exempt_log, OUTER mailmkt
 where company_code = "MV"
   and customer_number = txe_cust_id
   and company_code = txe_company
   and customer_number = mk_acct_bill
   and mk_division = "MVP";

The "OUTER" clause effectively makes the relation optional.  

Works much quicker than a subselect in Informix IDS,  not I've got to try 
it in PostgreSQL....

_______________________________________________
Members mailing list
Members@kalamazoolinux.org