Re: How do I turn 2 separate select queries into the columns of 1 select query

barrybevel_at_gmail.com wrote:
> Hi,
> I'm having a really hard time trying to figure this out so any help
> would be great.
>
> I have 2 independent sql queries which are very similar with just a
> different value in the where clause:
>
> SELECT weekscum as fabbed
> FROM mytable
> WHERE mytable.ccin_id = 1 AND mytable.plant_id = 1 AND mytable.mytype
> = 8
>
> SELECT weekscum as planned
> FROM mytable
> WHERE mytable.ccin_id = 1 AND mytable.plant_id = 1 AND mytable.mytype
> = 15
>
> Both queries return 1 column of data, (13 rows long)
>
> How do I create an SQL query that will have the output of each of
> these queries as a column
> e.g. 2 columns one called fabbed and the other planned
>
> I have tried the following query but I get 13*13 rows instead of just
> 13 rows!
>
> SELECT t1.planned, t2.fabbed FROM
> (
> SELECT weekscum as planned, mytable.ccin_id, mytable.plant_id
> FROM mytable
> WHERE mytable.ccin_id = 1 AND mytable.plant_id = 1 AND
> mytable.mytype_id = 15
> ) AS t1
> JOIN
> (
> SELECT weekscum as fabbed, mytable.ccin_id, mytable.plant_id
> FROM mytable
> WHERE mytable.ccin_id = 1 AND mytable.plant_id = 1 AND
> mytable.mytype_id = 8
> ) AS t2
> ON t2.ccin_id = t1.ccin_id AND t2.plant_id = t1.plant_id
>
> Thanks in advance to anyone who can help me.

You can use the rownum pseudo-column to join both query results:

select d1.d,d2.d from (select rownum rn,d from t where c=8) d1 full outer join (select rownum rn,d from t where c=15) d2 on d1.rn=d2.rn;

d=weekscum, c=mytype_id, etc...

Jerome

PS: Sorry about this new thread, but something prevented me to answer to the one you had started... Received on Thu Mar 27 2008 - 14:34:42 CDT