pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOUTER JOIN
7+ messages / 5 participants
[nested] [flat]
* OUTER JOIN
@ 2003-04-17 14:21 Marco Roda <MarcoRoda@amdosoft.com>
2003-04-23 17:55 ` Re: OUTER JOIN Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Marco Roda @ 2003-04-17 14:21 UTC (permalink / raw)
To: pgsql-sql
Hallo,
I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
problems with outer joins.
Here are the tables:
select * from shift_type
id | order_num | from_time | to_time | disabled
----+-----------+-----------+----------+----------
1 | 1 | 06:00:00 | 14:00:00 | f
2 | 2 | 14:00:00 | 22:00:00 | f
3 | 3 | 22:00:00 | 06:00:00 | t
6 | 6 | 00:00:00 | 23:00:00 | f
select * from shift_desc
app_language_id | shift_type_id | description
-----------------+---------------+-------------
1 | 1 | Morning
1 | 2 | Afternoon
1 | 3 | Night
2 | 6 | SHIFT_DE
1 | 6 | SHIFT_EN
and here is the SQL for Oracle:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST, shift_desc SD
WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
ORDER BY ST.order_num
The expected result should be:
id | order_num | from_time | to_time | disabled | description
----+-----------+-----------+----------+----------+-------------
1 | 1 | 06:00:00 | 14:00:00 | f |
2 | 2 | 14:00:00 | 22:00:00 | f |
3 | 3 | 22:00:00 | 06:00:00 | t |
6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
with description populated with NULL, as app_language_id not found.
It seems that Oracle's outer joins work on the basis the single record,
while PostgreSQL don't.
How to do it?
Thank you for your attention,
Marco Roda
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OUTER JOIN
2003-04-17 14:21 OUTER JOIN Marco Roda <MarcoRoda@amdosoft.com>
@ 2003-04-23 17:55 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2003-04-23 17:55 UTC (permalink / raw)
To: Marco Roda <MarcoRoda@amdosoft.com>; pgsql-sql
On Thursday 17 Apr 2003 3:21 pm, Marco Roda wrote:
> Hallo,
>
> I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
> problems with outer joins.
[snip]
> and here is the SQL for Oracle:
> SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
> SD.description
> FROM shift_type ST, shift_desc SD
> WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
> ORDER BY ST.order_num
>
> The expected result should be:
> id | order_num | from_time | to_time | disabled | description
> ----+-----------+-----------+----------+----------+-------------
> 1 | 1 | 06:00:00 | 14:00:00 | f |
> 2 | 2 | 14:00:00 | 22:00:00 | f |
> 3 | 3 | 22:00:00 | 06:00:00 | t |
> 6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
> with description populated with NULL, as app_language_id not found.
>
> It seems that Oracle's outer joins work on the basis the single record,
> while PostgreSQL don't.
> How to do it?
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST LEFT JOIN shift_desc SD
ON ST.id=SD.shift_type_id AND SD.app_language_id=2
ORDER BY ST.order_num;
That seems to work. Out of curiosity, what were you trying?
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* OUTER JOIN
@ 2003-04-17 14:30 Marco Roda <MarcoRoda@amdosoft.com>
2003-04-17 14:37 ` Re: OUTER JOIN Patrik Kudo <kudo@pingpong.net>
0 siblings, 1 reply; 7+ messages in thread
From: Marco Roda @ 2003-04-17 14:30 UTC (permalink / raw)
To: pgsql-sql
Hallo,
I need to port some SQL from Oracle to PostgreSQL v7.2, but I am finding
problems with outer joins.
Here are the tables:
select * from shift_type
id | order_num | from_time | to_time | disabled
----+-----------+-----------+----------+----------
1 | 1 | 06:00:00 | 14:00:00 | f
2 | 2 | 14:00:00 | 22:00:00 | f
3 | 3 | 22:00:00 | 06:00:00 | t
6 | 6 | 00:00:00 | 23:00:00 | f
select * from shift_desc
app_language_id | shift_type_id | description
-----------------+---------------+-------------
1 | 1 | Morning
1 | 2 | Afternoon
1 | 3 | Night
2 | 6 | SHIFT_DE
1 | 6 | SHIFT_EN
and here is the SQL for Oracle:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST, shift_desc SD
WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
ORDER BY ST.order_num
The expected result should be:
id | order_num | from_time | to_time | disabled | description
----+-----------+-----------+----------+----------+-------------
1 | 1 | 06:00:00 | 14:00:00 | f |
2 | 2 | 14:00:00 | 22:00:00 | f |
3 | 3 | 22:00:00 | 06:00:00 | t |
6 | 6 | 00:00:00 | 23:00:00 | f | SHIFT_DE
with description populated with NULL, as app_language_id not found.
It seems that Oracle's outer joins work on the basis the single record,
while PostgreSQL don't.
How to do it?
Thank you for your attention,
Marco Roda
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: OUTER JOIN
2003-04-17 14:30 OUTER JOIN Marco Roda <MarcoRoda@amdosoft.com>
@ 2003-04-17 14:37 ` Patrik Kudo <kudo@pingpong.net>
0 siblings, 0 replies; 7+ messages in thread
From: Patrik Kudo @ 2003-04-17 14:37 UTC (permalink / raw)
To: Marco Roda <MarcoRoda@amdosoft.com>; +Cc: pgsql-sql
Hello
I'd probably write the query something like this:
SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
SD.description
FROM shift_type ST LEFT OUTER JOIN shift_desc SD
ON (ST.id = SD.shift_type_id AND SD.app_language_id = 2)
ORDER BY ST.order_num
I haven't tried the query, so I'm not all sure it works though =)
Regards,
Patrik Kudo
Marco Roda wrote:
> Hallo,
>
>
> and here is the SQL for Oracle:
> SELECT ST.id, ST.order_num, ST.from_time, ST.to_time, ST.disabled,
> SD.description
> FROM shift_type ST, shift_desc SD
> WHERE ST.id = SD.shift_type_id(+) AND SD.app_language_id(+) = 2
> ORDER BY ST.order_num
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Outer join
@ 2004-02-03 06:28 Russell Shaw <rjshaw@iprimus.com.au>
2004-02-03 06:35 ` Re: Outer join Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 7+ messages in thread
From: Russell Shaw @ 2004-02-03 06:28 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm using postgresql 7.3.4 on debian. I get bad results
from a two-table left outer join.
First table: select * from descriptions;
desc_id | description
---------+-------------
909097 | cap
107890 | resis
223940 | ic
447652 | electro
(4 rows)
Second table: select * from parts;
part_id | desc_id | mounting | man_id
---------+---------+----------+--------
2 | 107890 | SMD | 7
1 | 909097 | LEADED | 1
3 | 223940 | LEADED | 8
(3 rows)
Join:
SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN
descriptions d ON p.desc_id=d.desc_id;
NOTICE: Adding missing FROM-clause entry for table "parts"
part_id | desc_id | mounting | man_id | description | desc_id
---------+---------+----------+--------+-------------+---------
2 | 107890 | SMD | 7 | resis | 107890
1 | 909097 | LEADED | 1 | resis | 107890
3 | 223940 | LEADED | 8 | resis | 107890
2 | 107890 | SMD | 7 | ic | 223940
1 | 909097 | LEADED | 1 | ic | 223940
3 | 223940 | LEADED | 8 | ic | 223940
2 | 107890 | SMD | 7 | cap | 909097
1 | 909097 | LEADED | 1 | cap | 909097
3 | 223940 | LEADED | 8 | cap | 909097
(9 rows)
/\ /\
|| ||
p.desc_id d.desc_id
I don't see why there are rows with p.desc_id and d.desc_id different.
(I learnt sql last week)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer join
2004-02-03 06:28 Outer join Russell Shaw <rjshaw@iprimus.com.au>
@ 2004-02-03 06:35 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-03 06:44 ` Re: Outer join Russell Shaw <rjshaw@iprimus.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Tomasz Myrta @ 2004-02-03 06:35 UTC (permalink / raw)
To: Russell Shaw <rjshaw@iprimus.com.au>; pgsql-sql
Dnia 2004-02-03 07:28, Użytkownik Russell Shaw napisał:
> Hi,
> I'm using postgresql 7.3.4 on debian. I get bad results
> from a two-table left outer join.
>
> First table: select * from descriptions;
>
> desc_id | description
> ---------+-------------
> 909097 | cap
> 107890 | resis
> 223940 | ic
> 447652 | electro
> (4 rows)
>
>
> Second table: select * from parts;
>
> part_id | desc_id | mounting | man_id
> ---------+---------+----------+--------
> 2 | 107890 | SMD | 7
> 1 | 909097 | LEADED | 1
> 3 | 223940 | LEADED | 8
> (3 rows)
>
>
> Join:
> SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN
^^^^^^^^^^^^^^^
You can't access "parts" here - you used table alias, so the only way to
access it is using "p.*"
> descriptions d ON p.desc_id=d.desc_id;
>
> NOTICE: Adding missing FROM-clause entry for table "parts"
Rewrite your query and show your results.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer join
2004-02-03 06:28 Outer join Russell Shaw <rjshaw@iprimus.com.au>
2004-02-03 06:35 ` Re: Outer join Tomasz Myrta <jasiek@klaster.net>
@ 2004-02-03 06:44 ` Russell Shaw <rjshaw@iprimus.com.au>
0 siblings, 0 replies; 7+ messages in thread
From: Russell Shaw @ 2004-02-03 06:44 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Tomasz Myrta wrote:
> Dnia 2004-02-03 07:28, Użytkownik Russell Shaw napisał:
>
>> Hi,
>> I'm using postgresql 7.3.4 on debian. I get bad results
>> from a two-table left outer join.
>>
>> First table: select * from descriptions;
>>
>> desc_id | description
>> ---------+-------------
>> 909097 | cap
>> 107890 | resis
>> 223940 | ic
>> 447652 | electro
>> (4 rows)
>>
>>
>> Second table: select * from parts;
>>
>> part_id | desc_id | mounting | man_id
>> ---------+---------+----------+--------
>> 2 | 107890 | SMD | 7
>> 1 | 909097 | LEADED | 1
>> 3 | 223940 | LEADED | 8
>> (3 rows)
>>
>>
>> Join:
>> SELECT parts.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN
>
> ^^^^^^^^^^^^^^^
> You can't access "parts" here - you used table alias, so the only way to
> access it is using "p.*"
>
>> descriptions d ON p.desc_id=d.desc_id;
>>
>> NOTICE: Adding missing FROM-clause entry for table "parts"
>
> Rewrite your query and show your results.
Thanks, it works now:)
SELECT p.*, d.description, d.desc_id FROM parts p LEFT OUTER JOIN descriptions d
ON p.desc_id=d.desc_id;
part_id | desc_id | mounting | man_id | description | desc_id
---------+---------+----------+--------+-------------+---------
2 | 107890 | SMD | 7 | resis | 107890
3 | 223940 | LEADED | 8 | ic | 223940
1 | 909097 | LEADED | 1 | cap | 909097
(3 rows)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-03 06:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-17 14:21 OUTER JOIN Marco Roda <MarcoRoda@amdosoft.com>
2003-04-23 17:55 ` Richard Huxton <dev@archonet.com>
2003-04-17 14:30 OUTER JOIN Marco Roda <MarcoRoda@amdosoft.com>
2003-04-17 14:37 ` Patrik Kudo <kudo@pingpong.net>
2004-02-03 06:28 Outer join Russell Shaw <rjshaw@iprimus.com.au>
2004-02-03 06:35 ` Re: Outer join Tomasz Myrta <jasiek@klaster.net>
2004-02-03 06:44 ` Re: Outer join Russell Shaw <rjshaw@iprimus.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox