Обсуждение: problem with joining two tables

hi, i've got this problem:
i have to manage a list of events in my city, publishing them into my web
site; each event has a 'content' (containing infos such as title, place,
description, etc..) and is linked to one or more dates. I I decided to
create 2 tables, one to store the 'content' (which has a unique id), the
other to store the dates of the events; i called the first table 'real_time'
and the second 'real_time_ts': each two has the filed 'id_real_time' which
is used to correlate the 'contents' with the 'dates'. Everything is going
well, but when a content has more than one 'date' i get the same content
repeated for the number of its dates... this is not what i want to retrieve
! i want to do a SELECT which gives me only one occurance for each content,
even when the content has more than one date of happening ! I tried
grouping, joining a.s.o. but i came to nothing... can anyone please help me
? i really can't get out of this :-(

the sql I'm using is:

select t.id_realtime, t.id_real_users, t.id_places, t.id_index,
 t.content, t.shown_place, t.titol,
 s.start_date, s.end_date, s.start_time, s.end_time
from real_time t, real_time_ts s
where
 t.id_index = <dtml-sqlvar real_time.id_index type="nb">
and
 t.id_real_time = s.id_real_time
and
 ( s.start_date >= <dtml-sqlvar now type="string">
   or
   s.end_date >= <dtml-sqlvar now type="string"> )
and
 t.prio = '1'
order by s.start_date, s.end_date


being "now" the current date given by ZopeTime().strftime('%Y-%m-%d')

tnx to anyone who has read my e-mail and would (eventually!) help me !

have a nice day,
Giorgio A.

On 21 May 2001 12:15:21 +0200, Giorgio A. wrote:
> well, but when a content has more than one 'date' i get the same content
> repeated for the number of its dates... this is not what i want to retrieve
> ! i want to do a SELECT which gives me only one occurance for each content,
> even when the content has more than one date of happening ! I tried


Sounds very confusing to me. You want only one result, NO MATTER WHICH ?
That doesn't make sense. Hm, perhaps you want to GROUP, ORDER and LIMIT
1 ?
You should draw some example tables, and an example result, so we better
understand
what  you want to achieve.

--

 Nabil Sayegh

You might try using DISTINCT in your query, e.g.
SELECT DISTINCT t.content, .....

Bill

----- Original Message -----
From: Giorgio A. <jh@libero.it>
To: <pgsql-novice@postgresql.org>
Sent: Monday, May 21, 2001 5:15 AM
Subject: [NOVICE] problem with joining two tables


> hi, i've got this problem:
> i have to manage a list of events in my city, publishing them into my web
> site; each event has a 'content' (containing infos such as title, place,
> description, etc..) and is linked to one or more dates. I I decided to
> create 2 tables, one to store the 'content' (which has a unique id), the
> other to store the dates of the events; i called the first table
'real_time'
> and the second 'real_time_ts': each two has the filed 'id_real_time' which
> is used to correlate the 'contents' with the 'dates'. Everything is going
> well, but when a content has more than one 'date' i get the same content
> repeated for the number of its dates... this is not what i want to
retrieve
> ! i want to do a SELECT which gives me only one occurance for each
content,
> even when the content has more than one date of happening ! I tried
> grouping, joining a.s.o. but i came to nothing... can anyone please help
me
> ? i really can't get out of this :-(
>
> the sql I'm using is:
>
> select t.id_realtime, t.id_real_users, t.id_places, t.id_index,
>  t.content, t.shown_place, t.titol,
>  s.start_date, s.end_date, s.start_time, s.end_time
> from real_time t, real_time_ts s
> where
>  t.id_index = <dtml-sqlvar real_time.id_index type="nb">
> and
>  t.id_real_time = s.id_real_time
> and
>  ( s.start_date >= <dtml-sqlvar now type="string">
>    or
>    s.end_date >= <dtml-sqlvar now type="string"> )
> and
>  t.prio = '1'
> order by s.start_date, s.end_date
>
>
> being "now" the current date given by ZopeTime().strftime('%Y-%m-%d')
>
> tnx to anyone who has read my e-mail and would (eventually!) help me !
>
> have a nice day,
> Giorgio A.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

ok, i solved-up the problem ! tnx to Nabil who helped me.

I've had already tried nesting two SELECTs, but without results... now your
answer made me think deeper at this solution, and i came to this solution (i
think it's quite self-explanatory even if you donnow Zope):

<dtml-in "dbpower_save.sql_search_real_time()" size=3 orphan=0>
<dtml-in "dbpower_save.sql_search_real_time_ts({
                           'now': ZopeTime().strftime('%Y-%m-%d'),
                           'real_time_ts.id_real_time': id_real_time })"
size=1 orphan=0>


bye
have a nice day
Giorgio A.