Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] "joining" table records

[GENERAL] "joining" table records

From
Moreno Andreo
Date:
I have this table

rec_code | rec_date   | int1 | int2 |
10         2014-07-21   3      4
11         2014-07-21   5
10       2015-01-04   1      2
11         2016-02-05   6

That's the result I need to get from a SELECT statement

rec_date   | int1 | int2 | tempvalue
2014-07-21   3      4      5
2015-01-04   1      2
2016-02-05                 6

(lines with matching date "joined" into one, using another column/alias to hold the "joined row" value)

I'm stuck while searching a way to "select int1, int2 from tbl where rec_code = 10" UNION (select int1 from tbl where
rec_code= 11) as tempvalue *where rec_date is the same* 
(read this as pseudocode... I also thought about a mix of DISTINCT and UNION, but have no idea on how to put things
together).

Is it so simple I can't see it?
Is it the hot weather and the broken conditioner? :-)
Do I need to RTFM more in deep? :-))

I don't think it matters, but postgresql 9.1.21 x64 on Windows 7

Hope I've been clear enough

Cheers,
Moreno.-

Re: [GENERAL] "joining" table records

From
"David G. Johnston"
Date:
On Wed, Jun 21, 2017 at 9:47 AM, Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
> I have this table
>
> rec_code | rec_date   | int1 | int2 |
> 10         2014-07-21   3      4
> 11         2014-07-21   5
> 10         2015-01-04   1      2
> 11         2016-02-05   6
>
> That's the result I need to get from a SELECT statement
>
> rec_date   | int1 | int2 | tempvalue
> 2014-07-21   3      4      5
> 2015-01-04   1      2
> 2016-02-05                 6

It seems you want something like:

SELECT ...
FROM (SELECT WHERE rec_code = 10) r10
FULL JOIN (SELECT WHERE rec_code = 11) r11 USING (rec_date)

David J.

Re: [GENERAL] "joining" table records

From
Moreno Andreo
Date:
Il 21/06/2017 19:11, David G. Johnston ha scritto:
> On Wed, Jun 21, 2017 at 9:47 AM, Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
>> I have this table
>>
>> rec_code | rec_date   | int1 | int2 |
>> 10         2014-07-21   3      4
>> 11         2014-07-21   5
>> 10         2015-01-04   1      2
>> 11         2016-02-05   6
>>
>> That's the result I need to get from a SELECT statement
>>
>> rec_date   | int1 | int2 | tempvalue
>> 2014-07-21   3      4      5
>> 2015-01-04   1      2
>> 2016-02-05                 6
> It seems you want something like:
>
> SELECT ...
> FROM (SELECT WHERE rec_code = 10) r10
> FULL JOIN (SELECT WHERE rec_code = 11) r11 USING (rec_date)
>
> David J.
>
Hi David,
     exactly what I needed!

(ok, I'm going to RTFM :-)) )

Thanks a lot!
Have a nice day,
Moreno.