X-Original-To: pgsql-php@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5E5E8475425 for ; Sun, 2 Feb 2003 16:53:56 -0500 (EST) Received: from smtp.comcast.net (smtp.comcast.net [24.153.64.2]) by postgresql.org (Postfix) with ESMTP id DF1D14753A1 for ; Sun, 2 Feb 2003 16:53:55 -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 <0H9P00FI9BGWM7@mtaout07.icomcast.net> for pgsql-php@postgresql.org; Sun, 02 Feb 2003 16:53:20 -0500 (EST) Received: from trini0.org (laptop.trini0.org [192.168.0.5]) by hivemind.trini0.org (Postfix) with ESMTP id 07C14294 for ; Sun, 02 Feb 2003 16:53:20 -0500 (EST) Date: Sun, 02 Feb 2003 16:53:12 -0500 From: Gerard Samuel Subject: Re: Left Join SQL Help To: pgsql-php@postgresql.org Message-id: <3E3D9348.7000104@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/4 X-Sequence-Number: 1855 Just for the archive's sake. I was able to get the expected results using this sql -> 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 AND w.uid != '-1' WHERE topic_id = 'AFAdDFoAPjvgJNzj' ORDER BY post_time LIMIT 10 OFFSET 0; Not a definate answer to the problem, but so far in the actual code it looks like a probable solution.. 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/