Re: [GENERAL] How do you live without OUTER joins?

From: "Bruce Bantos" <anon(at)mgfairfax(dot)rr(dot)com>
To: "admin" <admin(at)wtbwts(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] How do you live without OUTER joins?
Date: 2000-01-12 03:58:08
Message-ID: 02e901bf5cb1$3c9a5fa0$0200a8c0@rsdevelop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2000-01-12 03:58:53 Re: [GENERAL] PSQL Function() help....
Previous Message Palle Girgensohn 2000-01-12 03:35:15 blocksize problem