pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Left Join SQL Help
3+ messages / 1 participants
[nested] [flat]

* Left Join SQL Help
@ 2003-02-01 20:37  Gerard Samuel <gsam@trini0.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Gerard Samuel @ 2003-02-01 20:37 UTC (permalink / raw)
  To: pgsql-php

  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/





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Left Join SQL Help
@ 2003-02-01 21:26  Gerard Samuel <gsam@trini0.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Gerard Samuel @ 2003-02-01 21:26 UTC (permalink / raw)
  To: lonniev@predictableresponse.com; +Cc: pgsql-php

I added GROUP BY xxx.... to the sql and that seemed to display the 
results correctly.
Im going to add it to the code and and see how it works...


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/





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Left Join SQL Help
@ 2003-02-02 21:53  Gerard Samuel <gsam@trini0.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Gerard Samuel @ 2003-02-02 21:53 UTC (permalink / raw)
  To: 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@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/






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-02-02 21:53 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-01 20:37 Left Join SQL Help Gerard Samuel <gsam@trini0.org>
2003-02-01 21:26 ` Gerard Samuel <gsam@trini0.org>
2003-02-02 21:53 ` Gerard Samuel <gsam@trini0.org>

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