Hum... i tried it.. but i always get this sql error #1054 - Unknown column 'other_times' in 'field list'
do u know what can be that error? Thanks 2011/1/5 Frederick Cheung <[email protected]> > > > 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%[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]<rubyonrails-talk%[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.
