[KLUG Members] Advanced SQL Questions (Smart Views)
Adam Williams
members@kalamazoolinux.org
Fri, 3 May 2002 16:37:07 -0400 (EDT)
> 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....