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.. 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%[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.
