select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
and comp.com_cat_abbr not in (select com_cat_addr from company_category);
If sub selects work as advertised in postgresql
this is the same as the oracle syntax:
select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr(+);
bob
Bruce Bantos wrote:
> I know that this may seem like a stale topic. I am not complaining about
> outer joins not being available yet in PostgreSQL. I just want to know how
> you live without them. I am migrating a production system to PostgreSQL and
> I do not know how to duplicate the functionality.
>
> For example, how can I live without outer joins in the example below:
>
> In my current Oracle DB, I have a number of "lookup" tables that contain
> something like this:
>
> TABLE company_category:
>
> com_cat_abbr | com_cat_long
> --------------------------------------------------
> SB | Small Business
> LB | Large Business
> NP | Not for Profit
>
> etc.
>
> Then in my main table, lets say the "company" table I have:
>
> company_name | com_cat_abbr
> ------------------------------------------------------------
> Microsoft | LB
> United Way | NP
> Coca Cola | NULL
>
> If I allow nulls in my com_cat_abbr column above, then how could I do a
> simple query to show the company table with the full com_cat_long
> description? These alternatives do not appear attractive:
>
> - Don't allow nulls and force a default value in the com_cat_abbr column
> - Don't do the query - if you want to display it that way handle it in the
> client
> - get rid of the lookup table and store the full text in the company table
>
> I like to have the lookup tables because I use them in the front end client
> to populate pulldowns, they save storage space, they allow some limited
> flexibility in changing the definition for the abbreviation, and they allow
> administrators to be able to see the abbreviation and understand what they
> are looking at. When referential integrity becomes available, I will use
> these lookup tables to enforce integrity.
>
> What are my alternatives? What is everyone else doing in their Postgres
> system? Thanks.
>
> - B
>
> ************
--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis
