X-Original-To: pgsql-php@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6A3A5475FD9 for ; Sat, 1 Feb 2003 15:39:47 -0500 (EST) Received: from smtp.comcast.net (smtp.comcast.net [24.153.64.2]) by postgresql.org (Postfix) with ESMTP id 016734760CD for ; Sat, 1 Feb 2003 15:38:17 -0500 (EST) Received: from hivemind.trini0.org (bgp626680bgs.brick201.nj.comcast.net [68.39.132.244]) by mtaout03.icomcast.net (iPlanet Messaging Server 5.2 HotFix 1.09 (built Jan 7 2003)) with ESMTP id <0H9N008JJDBFGW@mtaout03.icomcast.net> for pgsql-php@postgresql.org; Sat, 01 Feb 2003 15:38:03 -0500 (EST) Received: from trini0.org (laptop.trini0.org [192.168.0.5]) by hivemind.trini0.org (Postfix) with ESMTP id 69FA6199 for ; Sat, 01 Feb 2003 15:38:02 -0500 (EST) Date: Sat, 01 Feb 2003 15:37:50 -0500 From: Gerard Samuel Subject: Left Join SQL Help To: pgsql-php@postgresql.org Message-id: <3E3C301E.2050005@trini0.org> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; 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 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/1 X-Sequence-Number: 1852 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/