From wahab@mimos.my Thu Feb 13 08:52:28 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 378A14758E6 for ; Thu, 13 Feb 2003 03:52:28 -0500 (EST) Received: from filter.mimos.my (filter.mimos.my [192.228.137.70]) by postgresql.org (Postfix) with ESMTP id B6FF94758C9 for ; Thu, 13 Feb 2003 03:52:26 -0500 (EST) Received: from ew.mimos.my (localhost.localdomain [127.0.0.1]) by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h1D8eMV02643 for ; Thu, 13 Feb 2003 16:40:22 +0800 Received: (from root@localhost) by ew.mimos.my (8.12.3/8.12.3) id h1D8qPB1013131 for pgsql-sql@postgresql.org; Thu, 13 Feb 2003 16:52:25 +0800 (MYT) (envelope-from wahab@mimos.my) Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197]) by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h1D8qO1t013035 for ; Thu, 13 Feb 2003 16:52:24 +0800 (MYT) (envelope-from wahab@mimos.my) Message-ID: <3E4B5CC3.2C10E63A@mimos.my> Date: Thu, 13 Feb 2003 16:52:19 +0800 From: Abdul Wahab Dahalan Reply-To: wahab@mimos.my Organization: Mimos Berhad X-Mailer: Mozilla 4.77 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: UNION or UNION ALL X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/) Content-Type: multipart/alternative; boundary="------------BD030C9DBC2828A42A9D040C" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/278 X-Sequence-Number: 11676 --------------BD030C9DBC2828A42A9D040C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I've 3 tables A,B,C records in table A like: id name b1 abc b2 xyz b3 rst records in table B like: id type b1 logistics b1 importer b2 logistics b3 logistics b3 exporter records in table C like: id offer title b1 sell car b1 buy car b1 sell van b2 sell car How shoul I do a query so that I can get a result like (The query will be based on B.type ='logistics') id name offer b1 abc sell b1 abc buy b1 abc sell b2 xyz sell b3 rst -- Any help very much appricated --------------BD030C9DBC2828A42A9D040C Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I've 3 tables A,B,C
records in table A like:
id          name
b1        abc
b2        xyz
b3        rst

records in table B like:
id        type
b1        logistics
b1        importer
b2        logistics
b3        logistics
b3        exporter

records in table C like:
id        offer        title
b1        sell        car
b1        buy        car
b1        sell        van
b2        sell        car

How shoul I do a query so that I can get a result like
(The query will be based on B.type ='logistics')
id        name       offer
b1        abc        sell
b1        abc        buy
b1        abc        sell
b2        xyz        sell
b3        rst            --

Any help very much appricated --------------BD030C9DBC2828A42A9D040C-- From jasiek@klaster.net Thu Feb 13 12:05:37 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A83DE474E4F for ; Thu, 13 Feb 2003 07:05:37 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 046FB474E42 for ; Thu, 13 Feb 2003 07:05:37 -0500 (EST) Received: from klaster.net (pb145.krakow.cvx.ppp.tpnet.pl [213.76.37.145]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id F2DFB2B884; Thu, 13 Feb 2003 12:57:31 +0100 (CET) Message-ID: <3E4B89E0.2060206@klaster.net> Date: Thu, 13 Feb 2003 13:04:48 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: wahab@mimos.my Cc: pgsql-sql@postgresql.org Subject: Re: UNION or UNION ALL References: <3E4B5CC3.2C10E63A@mimos.my> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/285 X-Sequence-Number: 11683 Abdul Wahab Dahalan wrote: > I've 3 tables A,B,C > records in table A like: > id name > b1 abc > b2 xyz > b3 rst > > records in table B like: > id type > b1 logistics > b1 importer > b2 logistics > b3 logistics > b3 exporter > > records in table C like: > id offer title > b1 sell car > b1 buy car > b1 sell van > b2 sell car > > How shoul I do a query so that I can get a result like > (The query will be based on B.type ='logistics') > id name offer > b1 abc sell > b1 abc buy > b1 abc sell > b2 xyz sell > b3 rst -- > > Any help very much appricated select id, name, offer from A join B using (_id) left join C using (_id) where type='logistics'; As you see, you don't need any UNION ;-) BTW - please - turn off HTML format in your mail client. Regards, Tomasz Myrta