Skip site navigation (1) Skip section navigation (2)

Re: Left Join SQL Help

From: Gerard Samuel <gsam(at)trini0(dot)org>
To: pgsql-php(at)postgresql(dot)org
Subject: Re: Left Join SQL Help
Date: 2003-02-02 21:53:12
Message-ID: 3E3D9348.7000104@trini0.org (view raw or flat)
Thread:
Lists: pgsql-php
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/




pgsql-php by date

Next:From: Ludwig LimDate: 2003-02-03 10:16:39
Subject: [OT] PHP for ASP programmers
Previous:From: Gerard SamuelDate: 2003-02-01 21:26:08
Subject: Re: Left Join SQL Help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group