On Jan 5, 8:31 pm, Fernando Leandro <[email protected]> wrote: > djangst, > > hum.... but actually in my database it occurs... that are some coursers that > have two times that reffers to the same day (because actually in my db, time > has the hour too, not only the day) > > but, using Fred`s example, i would have to make a select in the courses and > use that another select that fred used as a condition for this first select? > > like this? > > SELECT DISTINCT courses. * > FROM courses > INNER JOIN courses_times ON courses_times.course_id = courses.id > INNER JOIN times ON courses_times.time_id = times.id > AND times.dia = 'Monday' > WHERE EXISTS ( > > SELECT count( * ) AS times_scheduled > FROM courses_times > INNER JOIN courses_times AS other_times ON other_times.course_id = > courses_times.course_id > WHERE courses_times.time_id = times.id > GROUP BY courses_times.course_id > HAVING times_scheduled =1 > )
I think you can do it with a similar query to my first, something along the lines of select *, count(distinct other_times) as days_scheduled from course_times inner join times on course_times.time_id = times.id inner join course_times as other_course_times on course_times.course_id = other_course_times.course_id inner join times as other_times on other_times.id = other_course_times.time_id where times.day = 'Monday' group by course_id having days_scheduled = 1 You select course_times whose corresponding time has a day of monday. You then join the course_times and times of the same course_id and count the number of distinct days. In general, if you can write something without a dependant subquery, then you should. Fred > > Sorry for the question but i`m new to sql and all this stuff.. > > Thanks > > Fernando > > 2011/1/5 djangst <[email protected]> > > > > > Check out Fred's earlier example. Using the count of courses with only > > one courses_times row in conjunction with the having clause you can > > filter out courses scheduled on more than one day. > > > The only potential problem I could foresee with this would be if a > > course could be scheduled for multiple times on the same day. But > > that's not reflected in the data model you posted so it shouldn't be > > an issue. > > > On Jan 5, 1:50 pm, Fernando Leandro <[email protected]> > > wrote: > > > yes.. its exactly this... but how can i do that in another way? > > > -- > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Talk" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<rubyonrails-talk%2Bunsubscrib > > [email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
