Re: Question about SQL syntax

On Wed, 15 Nov 2000 05:09:01 GMT, gcouch_at_my-deja.com wrote:

>I apologize up front that this question may be a little long. I'm not
>sure the most accurate way to phrase it so that the relevant
>information is passed along. Thanks for your patience.
>--------
>
>I am returning data from an Oracle database into MS Access for further
>manipulation and reporting. I have a query that I'm not sure the
>correct syntax for. (I know basic SQL, but am mostly unfamiliar with
>Oracle).
>
>I have two tables (let's call them 'Tbl' and 'SubTbl'). There is a one-
>to-many relationship between Tbl and SubTbl. There is a common field
>(I'll call it 'Key') shared by the two tables.
>
>I know how to join the two tables, based on the Key, and retrieve the
>information I need from both tables. However, I would like
>to "columnize" the row data from SubTbl.
>
>In other words, I want to join the two tables based on the Key field,
>get the needed info from Tbl and selectively pull rows from SubTbl and
>put the SubTbl row data into columns in my Access table.
>
>For example: let's say field 1 in SubTbl can contain one of four
>choices ('A', 'B', 'C', or 'D'). Field 2 has a related value to field
>1. Let's assume it is a $ amount. I am only interested in the
>contents of Field2 from SubTbl where Field1 is either 'A' or 'C'.
>
>In my current query, I can accomplish retrieving the data from both
>tables, but I get two records for each Key, one for when Field1 in
>SubTbl is 'A' (along with the corresponding Field2 of course) and one
>for Field1 when it is 'C'.
>
>I had rather get one row for each Key with say a 'A-Field2value' and
>a 'C-Field2value' in my result so it is a straight record-for-record
>dump from the query results into my Access table.
>
>What SQL syntax is required to accomplish this task of moving row data
>into column data?
>
>Thanks in advance for the help.
>

How about

select key, a.field1, c.field1
from (select key, field1
from tbl, subtbl
where tbl.key = subtbl.key
and field1 = 'A') a, (select key, field1 from tbl, subtbl
where tbl.key = subtbl.key
and field1 = 'C') c
where a.key = c.key

If every key doesn't have an 'A' and a 'C' record, then you'll have to add an outer join.

-greg

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Sat Nov 18 2000 - 06:16:47 CST