Re: sql question (hopefully) - Mailing list pgsql-novice

thank you so much for the replies.  they're really appreciated.  =) please
bear with me and read on.

i wanted to find out for sure if i really won't get the desired result(s) in
sql and have to create a function instead.

yes it breaks the 1st normal form.  we did this for some *practical*
purposes supposedly. =) i would've wanted to discuss it here but i'll
probably bore you to death or i'd end up starting a new discussion
altogether.  since it's in production and i need to do a quick patch without
touching the external programs depending on this schema, i've to find a
quick remedy.

anyway, is there a way to convert the result of the 2nd sql statement as a
string literal such that:
"SELECT id FROM a_table WHERE id NOT IN (:result_of_2nd_sql_statement)"
(where ":result_of_2nd_sql_statement" is a variable) would work?

to be clear, because

"select id from b_table" yields an output of:
id
----
2,3,4

and not what it would've if i normalized the table instead:
id
----
2
3
4

is it possible to make the resulting sql statement become "select id from
a_table where id not in (2,3,4)" by just using a function inherent to sql
(such as 'to_char' or 'cast' or an unknown function to me that takes in the
result of an sql statement and converts it somehow)?

thus, the result of the 2nd sql statement which is "2,3,4" is substituted in
to my original sql statement.

it's easier to do it outside the db backend (C, JAVA, PERL, TCL or whatever)
but i'm really trying hard to achieve it from the backend.

anyway, i've tried the ff but haven't found the solution yet:
1) assign the result in the sql statement to a variable and proceed with
select #1 substituting the variable
2) "convert" the result of the sql statement "select id from b_table" into
characters to apply the 1st sql statement.

i'm currently creating a function that's suppose to do the trick because i'm
tired out from finding quicker remedies.

the idea of echoing the result of the 2nd sql statement to a file and then
echoing it back to the psql command line crossed my mind but it doesn't
appear to be a neat implementation if ever it would work.

any other ideas?

thanks again!  my apologies for being too wordy.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ron Johnson
Sent: Wednesday, June 18, 2003 9:19 PM
To: PgSQL Novice ML
Subject: Re: [NOVICE] sql question (hopefully)


On Wed, 2003-06-18 at 07:38, Mel Jamero wrote:
> will try to keep this as short as possible. =)
>
> 1. "select id from a_table where id not in (2,3,4)" works fine
>
> 2. "select id from a_table where id not in (select id from b_table)" works
> fine if the output of the "select id from b_table" looks like:
> id
> ----
>  2
>  3
>  4
>
> problem is "select id from b_table" in #2 statement has the following
> output:
> id
> ----
> 2,3,4
>
> how do i do execute the query in a single sql statement?

b_table.id has Repeating Values, which breaks the 1st Normal Form
of relational DBMS design.

Thus, redesign b_table so that id only has 1 value.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html