Ruby on Rails

On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...@gmail.com>
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 <djan...@gmail.com>

>
>
>
> > 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 <fernandoleandro1...@gmail.com>
> > 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 rubyonrails-talk@googlegroups.com.
> > To unsubscribe from this group, send email to

> > rubyonrails-talk+unsubscribe@googlegroups.com<rubyonrails-talk%2Bunsubscrib e@googlegroups.com>

> > .
> > 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 rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.