Re: Display of text fields - Mailing list pgsql-general

On Fri, 10 Sep 2004, Ennio-Sr wrote:

> * Stephan Szabo <sszabo@megazone.bigpanda.com> [100904, 07:10]:
> > On Fri, 10 Sep 2004, Ennio-Sr wrote:
> >
> > > I slightly modified your queries and the result gets nearer my goals,
> > > but ...
> > > Here is what I tried:
> > > [ ... ]
> >
> > As an explanation of the duplicate rows:
> >
> > FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
> > [ ... ]
> > If you're not using any other fields from t1, I would wonder if something
> > like:
> >
> > SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.note, 'n/a') as note
> > FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr='T' and
> > t0.n_prog=t1.n_prog) where t0._nprog<>0;
> >
> > would be closer to what you want from the query.  The join should give
> > output with either t0 extended by NULLs or t0 joined by t1 dependant on
> > whether t0.scheda_ltr='T' and if it finds a matching row in t1.
> >
> Thank you Stephen, for your contribution: I'll study it in due course
> ... as I'm interested to learn as much as possible ...
> However, in the meantime, I think I found the solution. What helped me
> was the construction of these two testing tables:
>
>               Table "foo"
>  Column |       Type        | Modifiers
> --------+-------------------+-----------
>  a      | integer           |
>  b      | character varying |
>  c      | character varying |
>  has_d  | character(1)      |
>
> # which I filled with:
>
>  a |  b   |   c    | has_d
> ---+------+--------+-------
>  1 | one  | number | Y
>  2 | two  | number | Y
>  3 | tree | name   | Y
>  4 | blue | color  | N
>  5 | john | person | N
> (5 rows)
>
> # and:
>
>         Table "foo_d"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
>  d      | text    |
>
>  # bearing my 'would-be' memo field:
>
>  a |                d
> ---+----------------------------------
>  1 | is the first natural
>  2 | follows 1 in the seq of natural
>  3 | there are various qualities of -
> (3 rows)
>
> # Then I launched an 'nth' variant of my query:
>
> SELECT DISTINCT
> ---    t0.a, t0.b, t0.c, t0.has_d, -- t1.d,
> --      t0.has_d,  -- ## ok, mostr prima i due 'N' e poi due volte quelli Y
> --                 ## se tolgo 't0.has_d', cambia ordine ma sempre 8
> --                 ## sono
>     CASE
>        WHEN t0.has_d = 'Y' AND t0.a=t1.a
>          THEN t0.a || ' - ' ||  t0.b   || ' - ' || t0.c || ' - ' || t1.d
>           ELSE
>            CASE
>          WHEN t0.has_d = 'N'
>                THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' ||  t0.has_d
>         END
>         END  AS "The result is: "
>     FROM foo t0, foo_d t1;

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

>                  The result is:
> ----------------------------------------------------
>  1 - one - number - is the first natural
>  2 - two - number - follows 1 in the seq of natural
>  3 - tree - name - there are various qualities of -
>  4 / blue / color
>  5 / john / person
>
> (6 rows)
>
> # which is exaclty what I was looking for :-)))))