OUTER JOIN with filter

From: "Nicolas Fertig" <nfertig(at)swissnow(dot)ch>
To: pgsql-sql(at)postgresql(dot)org
Subject: OUTER JOIN with filter
Date: 2003-03-01 18:53:27
Message-ID: 3e61011d$0$1220$5402220f@news.sunrise.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

Can anyone help me with the problem bellow ?

I want to have all the row in table "table_main" with the value in the table
"table_slave" (value or null if not exist)

It is possible to have the same result without sub-select in OUTER JOIN
(speed problem on big table) ?

Actualy this is what I make...

CREATE TABLE "table_main" (
"id" int4,
"some_field" varchar(100)
);

CREATE TABLE "table_slave" (
"id" int4,
"name" varchar(100),
"c1" varchar(30)
);

SELECT TM.some_field, TS.name
FROM table_main TM
LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS
USING(id)

the request bellow is not correct:

SELECT TM.some_filed, TS.name
FROM table_main TM
LEFT OUTER JOIN table_slave TS USING(id)
WHERE (TS.c1 = 'myc1' OR TS.c1 IS NULL)

Many thanks,

Nicolas

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Chad Thompson 2003-03-01 22:55:57 Re: Beginner needs help
Previous Message Greg Stark 2003-03-01 17:53:41 Re: Denormalizing during select