X-Original-To: pgsql-php@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AA83F475425 for ; Sat, 1 Feb 2003 16:26:42 -0500 (EST) Received: from smtp.comcast.net (smtp.comcast.net [24.153.64.2]) by postgresql.org (Postfix) with ESMTP id 16A0E474E53 for ; Sat, 1 Feb 2003 16:26:42 -0500 (EST) Received: from hivemind.trini0.org (bgp626680bgs.brick201.nj.comcast.net [68.39.132.244]) by mtaout07.icomcast.net (iPlanet Messaging Server 5.2 HotFix 1.09 (built Jan 7 2003)) with ESMTP id <0H9N003IWFJUXG@mtaout07.icomcast.net> for pgsql-php@postgresql.org; Sat, 01 Feb 2003 16:26:19 -0500 (EST) Received: from trini0.org (laptop.trini0.org [192.168.0.5]) by hivemind.trini0.org (Postfix) with ESMTP id E4A502EB; Sat, 01 Feb 2003 16:26:17 -0500 (EST) Date: Sat, 01 Feb 2003 16:26:08 -0500 From: Gerard Samuel Subject: Re: Left Join SQL Help To: lonniev@predictableresponse.com Cc: pgsql-php@postgresql.org Message-id: <3E3C3B70.8010809@trini0.org> MIME-version: 1.0 Content-type: text/plain; charset=us-ascii; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en, en-us, nl, th User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.1) Gecko/20021213 References: <00d101c2ca37$4c0eb700$113ea8c0@predictable> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/2 X-Sequence-Number: 1853 I added GROUP BY xxx.... to the sql and that seemed to display the results correctly. Im going to add it to the code and and see how it works... Lonnie VanZandt wrote: >Add a select DISTINCT clause, perhaps. > >-----Original Message----- >From: pgsql-php-owner@postgresql.org >[mailto:pgsql-php-owner@postgresql.org] On Behalf Of Gerard Samuel >Sent: Saturday, February 01, 2003 1:38 PM >To: pgsql-php@postgresql.org >Subject: [PHP] Left Join SQL Help > > > Im trying to write a "Left Join" sql statement. >There are 4 tables in a similar structure to -> >[user table] >user_id - username > >[user2 table] >uid - user_regdate > >[post table] >post_id - poster_id > >[posts_text table] >post_id - post_text > >[whosonline table] >uid - session_id > >This is the sql that Im using -> >SELECT u.user_id, u2.user_regdate, p.post_id, w.uid FROM posts p LEFT >JOIN posts_text pt ON p.post_id = pt.post_id LEFT JOIN users u ON >p.poster_id = u.user_id LEFT JOIN users2 u2 ON p.poster_id = u2.uid LEFT > >JOIN whosonline w ON p.poster_id = w.uid WHERE topic_id = >'AFAdDFoAPjvgJNzj' ORDER BY post_time LIMIT 10 OFFSET 0; > >If the whosonline table has more than one entry for "anonymous user" id >-1, the sql reports 5 rows. >The 2 last rows should just be one row. > > user_id | user_regdate | post_id | uid >------------------+--------------+------------------+----- > 1 | 1009861200 | a | >1 | 1009861200 | b | >1 | 1009861200 | c | > -1 | 0 | d | -1 > -1 | 0 | d | -1 >(5 rows) > > >Any help in this matter would be appreciated. Thanks. > > > -- Gerard Samuel http://www.trini0.org:81/ http://dev.trini0.org:81/