On Jan 5, 10:59 pm, Fernando Leandro <[email protected]> wrote: > Thanks Fred, > > Well.. i could make it work... > I dont know why, but it wastes about 25 seconds to give me query result... > > Is it normal? the number of registers its like 8000 for courses, 13000 for > coursers_times and 400 for times..
Depends on what indexes there are. You can use explain to see how the database is executing your query. Even a fairly small join will run incredibly slowly in the presence of insufficient indexes Fred > > It must have a better way to do this.. its a normal query... > > Thanks > > Fernando > > 2011/1/5 Frederick Cheung <[email protected]> > > > > > Should be other_times.day > > > Sent from my iPhone > > > On 5 Jan 2011, at 21:10, Fernando Leandro <[email protected]> > > wrote: > > > 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]> > > [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]>[email protected]. > >> > > To unsubscribe from this group, send email to > >> > > <rubyonrails-talk%[email protected]> > >> [email protected]<rubyonrails-talk%2Bunsubscrib > >> <[email protected]>[email protected]> > >> > > . > >> > > For more options, visit this group at > >> > > <http://groups.google.com/group/rubyonrails-talk?hl=en> > >>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]> > >> [email protected]. > >> To unsubscribe from this group, send email to > >> <rubyonrails-talk%[email protected]> > >> [email protected]. > >> For more options, visit this group at > >> <http://groups.google.com/group/rubyonrails-talk?hl=en> > >>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. > > > -- > > 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.
