Re: Using UNION ?!

From: Thomas T <T_at_T>
Date: Wed, 9 Apr 2003 11:30:17 -0400
Message-ID: <3e943c89$1@rutgers.edu>

"Nicolas Hernandez" <n.hernandez_at_genesis-sa.fr> wrote in message
news:3e93cf56$0$28759$626a54ce_at_news.free.fr...

> Hello, i am a real newbie in SQL n Oracle. I really need your Help.
>
> I gonna try to explain my probleme:
>
>
> I got two tables as this
>
>
> ACT
> |ACT_CODE|PROJ_CODE|ACT_LIB|CONSO|PREVI|ENGAGE|
>
> and this one
> SACT
> |SACT_CODE|ACT_CODE|ACT_LIB|CONSO|PREVI|ENGAGE|
>
>
> as you can see igot a link between ACT->SACT via ACT_CODE
>
> I need this sort of result:
>
> ACT value 1
> SACT value 1
> SACT value 2
> SACT value 3
> SACT value 4
> ACT value 2
> SACT value 5
> SACT value 6
> SACT value 7
> SACT value 8
>
> using the logical link between my two tables.
>
> How could i do that, only with SQL . double orderby ? with a union ?
>
> at this time
> i just have :-(
>
> for {
> | ACT
> | SACT
> | SACT
> UNION
> | ACT
> | SACT
> | SACT
> }
>
> thanx a lot in advance
> Nicolas
>

Nicolas,

Does your "value" in your expected output correspond to ACT_CODE and SACT_CODE, respectively? For each ACT_CODE, you have a few SACT_CODEs? If so,here's my guess... since you need the same column names for a union (or have I been mistaken in that for years?):

select myname, myvalue
  from
  (
    select a.act_code as avalue, 'act' as myname, a.act_code as myvalue     from act a
  union
    select a.act_code as avalue, 'sact' as myname, s.sact_code as myvalue     from act a, sact s
    where a.act_code=s.act_code
      and rownum<50
)
order by avalue
/

I should probably explain this one a bit! This uses an inner query (the union'd select statements) as a "fake table" for the outer query.

The inner union; first query: Pulls all your act codes. Note that the act_code is repeated TWICE, with different column names, giving:

avalue (act_code), myname, myvalue (act_code again) 1, act, 1
2, act, 2

The inner union, second query: Pulls all the sact_codes, based on your act_codes. Alone, this gives something like

avalue (act_code), myname, myvalue (sact_code)

1, sact, 1
1, sact, 2
1, sact, 3
1, sact, 4

But you said you wanted the act_code listed first, and THEN all your sact_codes, right? You want something like:

1
sact 1
sact 2
sact 3
sact 4

Here's where the union comes in. The union returns results similar to:

1 act 1 (from first query in join)

1 sact 1    (from second query in join)
1 sact 2    (2nd query)
1 sact 3    (2nd query)
1 sact 4    (2nd query)

2 act 2 (from first query in join)

2 sact 5    (2nd query)
2 sact 6    (2nd query)
2 sact 7    (2nd query)
2 sact 8    (2nd query)

Again, not what you wanted. That's where the outermost query comes in. A row from the first query has (avalue, myname, myvalue) = (1, 'act', 1). A row from the second query has (avalue, myname, myvalue) = (1, 'sact', 4).

For the outermost query, by pulling only the last TWO columns of those rows, and ordering by the FIRST (unseen) column, you get your results.

act 1 (2nd and 3rd columns of first "union" query)

sact 1     (2nd and 3rd columns of SECOND "union" query)
sact 2     (2nd and 3rd columns of SECOND "union" query)
sact 3     (2nd and 3rd columns of SECOND "union" query)
sact 4     (2nd and 3rd columns of SECOND "union" query)
act 2    (2nd and 3rd columns of first "union" query)
sact 5     (2nd and 3rd columns of SECOND "union" query)
sact 6     (2nd and 3rd columns of SECOND "union" query)
sact 7     (2nd and 3rd columns of SECOND "union" query)
sact 8     (2nd and 3rd columns of SECOND "union" query)

Give it a try, and please reply to this newsgroup to let us know if it works against your "real" database. The rownum<50 was just so I would only get 50 rows out of my test database; you can leave it off. You can actually understand how this works by running each of those two inner select statements separately. Then, run the union of the inner select statements separately. Finally, run the outermost query against the union. Keep in mind, if you're an "SQL newbie", most queries aren't this complicated! The problem lies in that you want one row returned from one table, and then multiple rows returned from another table. A simple solution that didn't yield your requested output would have been similar to:

break on act_code skip 1
select a.act_code, sact_code
from act a, sact s
where a.act_code=s.act_code
order by a.act_code;

But, I think you knew that! (grins) Are

And, to the group... Is there a more simple way out there for getting his requested output? I suppose two temporary views could also have been created, too, but then the "sort" by ACT_CODE would still need a third column in the view.

-Thomas Received on Wed Apr 09 2003 - 10:30:17 CDT