Обсуждение: Select statement problem

Hello,

I know the question may appear lame but I have problem with a SELECT
statement for a report. I have two separate statements:
SELECT t1.col1, t1.col2, SUM(t2.end_date::DATE - t2.start_date::DATE)
   from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
t3.col4 = t1.col4;

SELECT (Now()::DATE - t2.start_date::DATE) AS days
   from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
t3.col4 = t1.col4 and t2.end_date is null;

What I need is to have both statements' results presented in one table
which has 4 columns (t1.col1, t1.col2, "SUM", days). The tables used
are the same in both statements.
--
Łukasz Brodziak
II MU Bioinformatyka

Lukasz Brodziak wrote:
> Hello,
>
> I know the question may appear lame but I have problem with a SELECT
> statement for a report. I have two separate statements:
> SELECT t1.col1, t1.col2, SUM(t2.end_date::DATE - t2.start_date::DATE)
>    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
> t3.col4 = t1.col4;
>
> SELECT (Now()::DATE - t2.start_date::DATE) AS days
>    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
> t3.col4 = t1.col4 and t2.end_date is null;
>
> What I need is to have both statements' results presented in one table
> which has 4 columns (t1.col1, t1.col2, "SUM", days). The tables used
> are the same in both statements.


It is not clear how these two queries should be joined; but you want to
use something like this...

SELECT sql1.col1, sql1.col2, sql1.sum, sql2.days from (

SELECT t1.col3, t1.col4, t1.col1, t1.col2,
    SUM(t2.end_date::DATE - t2.start_date::DATE) as sum
    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
    t3.col4 = t1.col4

) sql1 left join (

SELECT t1.col3, t1.col4, t1.col1, t1.col2,
    (Now()::DATE - t2.start_date::DATE) AS days
    from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
    t3.col4 = t1.col4 and t2.end_date is null

) sql2 on sql1.col3 = sql2.col3 and sql1.col4 = sql2.col4

Frank

They ought to be joined just like You've written but there is a small
bug :) when I run the statement against physical data if _ have one
person who has procedures which have end_date filled and procedures
with null end_date (opened) they appear in results as two separate
rows one with the 'SUM' column filled and days column null and the
other is all the way round.

2010/10/8 Frank Bax <fbax@sympatico.ca>:
> It is not clear how these two queries should be joined; but you want to use
> something like this...
>
> SELECT sql1.col1, sql1.col2, sql1.sum, sql2.days from (
>
> SELECT t1.col3, t1.col4, t1.col1, t1.col2,
>   SUM(t2.end_date::DATE - t2.start_date::DATE) as sum
>   from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
>   t3.col4 = t1.col4
>
> ) sql1 left join (
>
> SELECT t1.col3, t1.col4, t1.col1, t1.col2,
>   (Now()::DATE - t2.start_date::DATE) AS days
>   from table1 t1, table2 t2, table3 t3 WHERE t1.col3 = t2.col3 and
>   t3.col4 = t1.col4 and t2.end_date is null
>
> ) sql2 on sql1.col3 = sql2.col3 and sql1.col4 = sql2.col4
>
> Frank
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



--
Łukasz Brodziak
II MU Bioinformatyka