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

Okay 3rd try:

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

This is the same as bruce m.

The real problem then is there is no generic way because you are forced to add enough nulls to pad out the other table.

>>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?
The union is necessary because the "or" causes a cartesian product on the 2 clauses.

Robert Davis wrote:

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

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