pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gerard Samuel <gsam@trini0.org>
To: pgsql-php@postgresql.org
Subject: Left Join SQL Help
Date: Sat, 01 Feb 2003 15:37:50 -0500
Message-ID: <3E3C301E.2050005@trini0.org> (raw)

  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/





view thread (3+ messages)  latest in thread

Message-ID: <3E3C301E.2050005@trini0.org>
Permalink:  ../3E3C301E.2050005@trini0.org/
Also on:    postgresql.org/message-id/3E3C301E.2050005@trini0.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: gsam@trini0.org
  Subject: Re: Left Join SQL Help
  In-Reply-To: <3E3C301E.2050005@trini0.org>
  References: <3E3C301E.2050005@trini0.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox