Re: [GENERAL] How do you live without OUTER joins? - Mailing list pgsql-general

Whoops. The "and" should be an "or".

select * from company comp, company_category cat
where comp.com_cat_abbr = cat.com_cat_abbr
or comp.com_cat_abbr not in (select com_cat_addr from company_category);

I read the other posts on a different named thread and they seem to indicate a "union" is necessary.
I am not sure why though?

Robert Davis wrote:

> 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
>
> ************

--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis