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