Re: Left Join SQL Help

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(at)postgresql(dot)org
>[mailto:pgsql-php-owner(at)postgresql(dot)org] On Behalf Of Gerard Samuel
>Sent: Saturday, February 01, 2003 1:38 PM
>To: pgsql-php(at)postgresql(dot)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/