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

How about using a union?

select cc.com_cat_long, co.company_name
from company_category cc, company co
where cc.com_cat_abbr = co.com_cat_abbr
union
select 'unknown' as com_cat_long, company_name
from company
where com_cat_abbr is null;


You still won't see entries for companies which have been assigned
an abbreviation that isn't in the company_category table.  I suppose
you'll have some other way to enforce the data integrity.

I hope this helps.

Sarah Officer
officers@aries.tucson.saic.com

Bruce Bantos wrote:
>
> > I'm not sure if this is what you're looking for, but you can try:
> > select company_category.com_cat_long, company.company_name from
> > company_category, company where
> > company_category.com_cat_abbr=company.com_cat_abbr;
>
> A simple join like that will inlcude only the company records with a
> com_cat_abbr equal to an entry in the company_category table. You would not
> get all the records in the company table. In the example below, you would
> only get records for Microsoft and the United Way...the Coca Cola entry
> would not be included in the query. Thus the need for outer joins....
>
> >
> > This is what I use myself, but I'm not quite sure this is the best
> > solution. If I could have feedback from other people as well, I'd be very
> > greatful also.
> >
> > > 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
> > >
> > >
> > >
> > > ************
> > >
> > >
> >
>
> ************