Thread: 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.