Hello guys,
I have a problem with how to do a sql query.
I have 3 tables: courses, times and courses_times
So for example, if i have the following data:
Courses courses_times Times
id | name course_id | time_id id | day
1 | Math 1 | 1 1 |
Monday
2 | Chimestry 1 | 2 2 |
Wednesday
3 | History 1 | 3 3 |
Friday
4 | Geografy 2 | 1
2 | 2
3 | 1
So, now, i want to make a query that returns to me only the courses that are
ministred on Monday,
but just ONLY Monday. For example, if a course is ministred Monday and
Wednesday i dont want.
In this example, i would like that my query returns just History, because is
the only course
that is just on Monday.
SELECT 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.day = ''Monday''
When i use a query like above, for example, it returns to me all the courses
that have association with Monday,
it returns Math, Chimestry and History, and i would like to receive just
History
I know that this question is not about rails but i need to solve this
problem to continue my rails app,
and i would appreciate if someone could help me..
Does anyone have a solution for this?
Thanks a lot
Fernando
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On 5 January 2011 17:38, Fernando Leandro <fernandoleandro1991-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello guys, > > I have a problem with how to do a sql query. > > I have 3 tables: courses, times and courses_times > > So for example, if i have the following data: > > Courses courses_times Times > id | name course_id | time_id id | day > > 1 | Math 1 | 1 1 | > Monday > 2 | Chimestry 1 | 2 2 | > Wednesday > 3 | History 1 | 3 3 | > Friday > 4 | Geografy 2 | 1 > 2 | 2 > 3 | 1 > > > So, now, i want to make a query that returns to me only the courses that are > ministred on Monday, > but just ONLY Monday. For example, if a course is ministred Monday and > Wednesday i dont want. > In this example, i would like that my query returns just History, because is > the only course > that is just on Monday. > > > SELECT 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.day = ''Monday'' > > When i use a query like above, for example, it returns to me all the courses > that have association with Monday, > it returns Math, Chimestry and History, and i would like to receive just > History > > > > I know that this question is not about rails but i need to solve this > problem to continue my rails app, > and i would appreciate if someone could help me..You can make it a Rails question by re-formulating the question in terms of your ActiveRecord relationships and asking how to code a find operation to return the objects you want. In practice this should be your initial approach anyway, only resorting to SQL if you cannot code it using Rails helpers. Colin -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
Colin Law wrote in post #972572:> On 5 January 2011 17:38, Fernando Leandro > <fernandoleandro1991-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> >> FROM courses >> >> I know that this question is not about rails but i need to solve this >> problem to continue my rails app, >> and i would appreciate if someone could help me.. > > You can make it a Rails question by re-formulating the question in > terms of your ActiveRecord relationships and asking how to code a find > operation to return the objects you want. In practice this should be > your initial approach anyway, only resorting to SQL if you cannot code > it using Rails helpers.Agreed -- mostly. I''m very comfortable with SQL, so I often find it helpful to consider a query in SQL terms first, and then figure out how to abstract that SQL with ActiveRecord. That usually leads to better queries anyway -- ActiveRecord can be ridiculously inefficient for certain operations.> > ColinBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wed, Jan 5, 2011 at 5:50 PM, Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:> Colin Law wrote in post #972572: > > On 5 January 2011 17:38, Fernando Leandro > > <fernandoleandro1991-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> > >> > >> FROM courses > >> > >> I know that this question is not about rails but i need to solve this > >> problem to continue my rails app, > >> and i would appreciate if someone could help me.. > > > > You can make it a Rails question by re-formulating the question in > > terms of your ActiveRecord relationships and asking how to code a find > > operation to return the objects you want. In practice this should be > > your initial approach anyway, only resorting to SQL if you cannot code > > it using Rails helpers. > > Agreed -- mostly. I''m very comfortable with SQL, so I often find it > helpful to consider a query in SQL terms first, and then figure out how > to abstract that SQL with ActiveRecord. That usually leads to better > queries anyway -- ActiveRecord can be ridiculously inefficient for > certain operations. > > Could you name some of these certain operations where its better to go forSQL queries instead of ActiveRecord. Also, is ARel also inefficient for the certain operations that you are mentioning?? Until now, I have learned that you should not write SQl queries in your code, rather go for Active Record, as it allows you to change your choice of database later on. But, If you are certain that you are going to use MySQL or a specific DB for an app then I guess there is no problem in going for SQL queries. Please correct me, if I am wrong somewhere.> > > > Colin > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > Posted via http://www.ruby-forum.com/. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
It sounds like you want a "NOT IN" subquery. On Jan 5, 12:38 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have 3 tables: courses, times and courses_times-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
Jatin Kumar wrote in post #972576:> On Wed, Jan 5, 2011 at 5:50 PM, Marnen Laibow-Koser > <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote: > >> > >> certain operations. >> >> Could you name some of these certain operations where its better to go for > SQL queries instead of ActiveRecord.I didn''t mean to use raw SQL *instead of* ActiveRecord. But sometimes ActiveRecord''s abstractions aren''t terribly good (notably with bulk and aggregate operations, as well as certain types of joins), and find_by_sql becomes necessary.> Also, is ARel also inefficient for the certain operations that you are > mentioning??I haven''t used Arel yet, so I''m not sure.> Until now, I have learned that you should not write SQl queries in your > code, rather go for Active Record, as it allows you to change your > choice of > database later on.That is true.> But, If you are certain that you are going to use > MySQL > or a specific DB for an app then I guess there is no problem in going > for > SQL queries.Not quite. If I need literal SQL (which is rare), my practice is to write it as far as possible in terms compliant with standard ANSI SQL syntax, without proprietary extensions. This gives the best portability across databases.> Please correct me, if I am wrong somewhere.Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wed, Jan 5, 2011 at 6:10 PM, Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:> Jatin Kumar wrote in post #972576: > > On Wed, Jan 5, 2011 at 5:50 PM, Marnen Laibow-Koser > > <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote: > > > >> > > >> certain operations. > >> > >> Could you name some of these certain operations where its better to go > for > > SQL queries instead of ActiveRecord. > > I didn''t mean to use raw SQL *instead of* ActiveRecord. But sometimes > ActiveRecord''s abstractions aren''t terribly good(notably with bulk and> aggregate operations, as well as certain types of joins),Could you elaborate a little bit on these operations and joins, name a few and tell why ActiveRecord isn''t good with them. I am pretty low on my learning curve in this domain, so your experience would really help. and> find_by_sql becomes necessary. > > > Also, is ARel also inefficient for the certain operations that you are > > mentioning?? > > I haven''t used Arel yet, so I''m not sure. > > > Until now, I have learned that you should not write SQl queries in your > > code, rather go for Active Record, as it allows you to change your > > choice of > > database later on. > > That is true. > > > But, If you are certain that you are going to use > > MySQL > > or a specific DB for an app then I guess there is no problem in going > > for > > SQL queries. > > Not quite. If I need literal SQL (which is rare), my practice is to > write it as far as possible in terms compliant with standard ANSI SQL > syntax, without proprietary extensions. This gives the best portability > across databases. > > > Please correct me, if I am wrong somewhere. > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > Posted via http://www.ruby-forum.com/. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 5, 5:38 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> When i use a query like above, for example, it returns to me all the courses > that have association with Monday, > it returns Math, Chimestry and History, and i would like to receive just > History > > I know that this question is not about rails but i need to solve this > problem to continue my rails app, > and i would appreciate if someone could help me.. > > Does anyone have a solution for this?In sql terms, how about select *, count(*) as times_scheduled from course_times inner join course_times as other_times on other_times.course_id course_times.course_id where course_times.time_id = 1 group by course_times.course_id having times_scheduled = 1 Arel is supposed to be way better than old skool activerecord at generating joins like this where you need to be careful about aliasing table names and so on. Fred -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
Actually, i tried to make this query using NOT IN, i made something like
this:
SELECT courses.*
FROM courses
INNER JOIN courses_times ON courses_times.course_id = courses.id
INNER JOIN times ON courses_times.time_id = times.id
where courses.id
NOT IN (
SELECT courses.id
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.day != ''Monday''
)
AND times.day = ''Monday''
But, its not very useful, im using MySql, and it wasted like 30 seconds to
give me the result.. o.O
its because my tables have a lot of registers: coursers (8000), times
(300), courses_times (1300)
Does anyone have another ideia for this query?
Fernando
2011/1/5 Frederick Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
>
>
> On Jan 5, 5:38 pm, Fernando Leandro
<fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > When i use a query like above, for example, it returns to me all the
> courses
> > that have association with Monday,
> > it returns Math, Chimestry and History, and i would like to receive
just
> > History
> >
> > I know that this question is not about rails but i need to solve this
> > problem to continue my rails app,
> > and i would appreciate if someone could help me..
> >
> > Does anyone have a solution for this?
>
> In sql terms, how about
>
> select *, count(*) as times_scheduled from course_times
> inner join course_times as other_times on other_times.course_id >
course_times.course_id
> where course_times.time_id = 1
> group by course_times.course_id
> having times_scheduled = 1
>
> Arel is supposed to be way better than old skool activerecord at
> generating joins like this where you need to be careful about aliasing
> table names and so on.
>
> Fred
>
> --
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Jatin Kumar wrote in post #972585:> On Wed, Jan 5, 2011 at 6:10 PM, Marnen Laibow-Koser > <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote: > >> >> I didn''t mean to use raw SQL *instead of* ActiveRecord. But sometimes >> ActiveRecord''s abstractions aren''t terribly good > > (notably with bulk and >> aggregate operations, as well as certain types of joins), > > Could you elaborate a little bit on these operations and joins, name a > few > and tell why ActiveRecord isn''t good with them. I am pretty low on my > learning curve in this domain, so your experience would really help.Well, for aggregate operations, AR just doesn''t provide much abstraction at all. Yes, User.count and User.max :age are helpful, but more complex aggregate operations (for example, on associated tables) have to be done in SQL, or in the application layer. Many Rails developers would opt for the latter, but IMHO that''s inefficient and silly, since the DB can do these operations for more efficiently. Perhaps Arel has changed this. I gather that this is probably a goal, but I don''t know if it''s been achieved. Bulk updates are another area where AR is really inefficient, since update_all is pretty anemic. ar-extensions can help here, though. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wed, Jan 5, 2011 at 6:40 PM, Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:> Jatin Kumar wrote in post #972585: > > On Wed, Jan 5, 2011 at 6:10 PM, Marnen Laibow-Koser > > <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote: > > > >> > >> I didn''t mean to use raw SQL *instead of* ActiveRecord. But sometimes > >> ActiveRecord''s abstractions aren''t terribly good > > > > (notably with bulk and > >> aggregate operations, as well as certain types of joins), > > > > Could you elaborate a little bit on these operations and joins, name a > > few > > and tell why ActiveRecord isn''t good with them. I am pretty low on my > > learning curve in this domain, so your experience would really help. > > Well, for aggregate operations, AR just doesn''t provide much abstraction > at all. Yes, User.count and User.max :age are helpful, but more complex > aggregate operations (for example, on associated tables) have to be done > in SQL, or in the application layer. Many Rails developers would opt > for the latter, but IMHO that''s inefficient and silly, since the DB can > do these operations for more efficiently. > > Perhaps Arel has changed this. I gather that this is probably a goal, > but I don''t know if it''s been achieved. > > Bulk updates are another area where AR is really inefficient, since > update_all is pretty anemic. ar-extensions can help here, though. > > Thanks.> Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > Posted via http://www.ruby-forum.com/. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I think your subquery may be too complex. Don''t you just want to select ids from Times where the day isn''t ''Monday'' and use that set as the filter on the top-level query? On Jan 5, 1:35 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Actually, i tried to make this query using NOT IN, i made something like > this:-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
djangst, yes.. its exactly this... but how can i do that in another way? Thanks 2011/1/5 djangst <djangst-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> I think your subquery may be too complex. Don''t you just want to > select ids from Times where the day isn''t ''Monday'' and use that set as > the filter on the top-level query? > > On Jan 5, 1:35 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > Actually, i tried to make this query using NOT IN, i made something like > > this: > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
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 ) Sorry for the question but i`m new to sql and all this stuff.. Thanks Fernando 2011/1/5 djangst <djangst-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > > > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
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 <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> > > On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > > > > > > > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > . > > > To unsubscribe from this group, send email to > > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org><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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Should be other_times.day Sent from my iPhone On 5 Jan 2011, at 21:10, Fernando Leandro <fernandoleandro1991-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > > > > > > > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > > To unsubscribe from this group, send email to > > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Have you considered an inner and outer join to the courses_times table? select distinct courses.* from courses inner join course_times monday_courses on courses.id monday_courses.course_id and monday_courses.time_id = 1 left outer join course_times non_monday_courses on courses.id non_monday_courses.course_id and non_monday_courses.time_id <> 1 where non_monday_courses.id is null On Jan 5, 12:38 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello guys, > > I have a problem with how to do a sql query. > > I have 3 tables: courses, times and courses_times > > So for example, if i have the following data: > > Courses courses_times Times > id | name course_id | time_id id | day > > 1 | Math 1 | 1 1 | > Monday > 2 | Chimestry 1 | 2 2 | > Wednesday > 3 | History 1 | 3 3 | > Friday > 4 | Geografy 2 | 1 > 2 | 2 > 3 | 1 > > So, now, i want to make a query that returns to me only the courses that are > ministred on Monday, > but just ONLY Monday. For example, if a course is ministred Monday and > Wednesday i dont want. > In this example, i would like that my query returns just History, because is > the only course > that is just on Monday. > > SELECT 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.day = ''Monday'' > > When i use a query like above, for example, it returns to me all the courses > that have association with Monday, > it returns Math, Chimestry and History, and i would like to receive just > History > > I know that this question is not about rails but i need to solve this > problem to continue my rails app, > and i would appreciate if someone could help me.. > > Does anyone have a solution for this? > > Thanks a lot > > Fernando-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
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 <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>> Should be other_times.day > > Sent from my iPhone > > On 5 Jan 2011, at 21:10, Fernando Leandro <fernandoleandro1991-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > 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 < <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > >> >> >> On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >> 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >> > >> > >> > >> > > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >> > > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> > > To unsubscribe from this group, send email to >> > > <rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> >> rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscrib >> <e@googlegroups.com>e@googlegroups.com> >> > > . >> > > 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 <rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> >> rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> To unsubscribe from this group, send email to >> <rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> >> rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 5, 10:59 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > > > Should be other_times.day > > > Sent from my iPhone > > > On 5 Jan 2011, at 21:10, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8@public.gmane.orgm> > > 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 < <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > >> On Jan 5, 8:31 pm, Fernando Leandro <fernandoleandro1...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > >> 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > >> > > 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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > >> > > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > >> > > To unsubscribe from this group, send email to > >> > > <rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > >> rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscrib > >> <e@googlegroups.com>e@googlegroups.com> > >> > > . > >> > > 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 <rubyonrails-talk-/JYPxA39Uh4Ykp1iOSErHA@public.gmane.orgm> > >> rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > >> To unsubscribe from this group, send email to > >> <rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > >> rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > >> 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
ppgengler-vA8bmGSXo1KakBO8gow8eQ@public.gmane.org
2011-Jan-06 17:34 UTC
Re: How to make this SQL Query?
On Jan 5, 12:10 pm, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Jatin Kumar wrote in post #972576: > > > But, If you are certain that you are going to use > > MySQL > > or a specific DB for an app then I guess there is no problem in going > > for > > SQL queries. > > Not quite. If I need literal SQL (which is rare), my practice is to > write it as far as possible in terms compliant with standard ANSI SQL > syntax, without proprietary extensions. This gives the best portability > across databases.Just tacking on another suggestion to this if people are reading back through here: if you do need literal SQL its a good idea to put it in a configuration file with a lookup key (i.e. :count_all_my_angry_birds); that way if you switch db engines or support multiple ones all your specific SQL is in one location that you can ensure works for whatever different dbs you need to support. And of course keep that file as ANSI compliant so that there are as little changes required as possible. \Peter -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.
ppgengler-vA8bmGSXo1KakBO8gow8eQ@public.gmane.org wrote in post #972869:> On Jan 5, 12:10pm, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >> syntax, without proprietary extensions. This gives the best portability >> across databases. > > Just tacking on another suggestion to this if people are reading back > through here: if you do need literal SQL its a good idea to put it in > a configuration file with a lookup key > (i.e. :count_all_my_angry_birds);Why not just use a named scope (or the Rails 3 equivalent)? That''s what I tend to do for complex queries. Granted, you don''t get all the SQL in one file, but that''s a *good* thing: it means you''re looking at the SQL in context. I want to like your config file idea, but I think it''s just reinventing stored procedures in a way that removes their remaining advantages.> that way if you switch db engines or > support multiple ones all your specific SQL is in one location that > you can ensure works for whatever different dbs you need to support. > And of course keep that file as ANSI compliant so that there are as > little changes required as possible....in which case your proposed solution isn''t necessary anyway. :)> > \PeterBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Hi thoen, Well.. i tried to use your query... select distinct courses.* from courses inner join course_times monday_courses on courses.id monday_courses.course_id and monday_courses.time_id = 1 left outer join course_times non_monday_courses on courses.id non_monday_courses.course_id and non_monday_courses.time_id <> 1 where non_monday_courses.id is null but, actually, in my database, at times table, i have another attribute like the hour of the course.... and so, i cant ask non_monday_courses.time_id <> 1, because actually i dont know if what i want to search is the register of id =1 of my courses_times table, i only know things about times... i have to make a join in the times table and ask times.day <> ''Seg'', could you understand? i wasn''t so clear... how could i make your query but using times.day <> ''Seg'' instead of non_monday_courses <> 1 ??? Thanks Fernando 2011/1/6 Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>> ppgengler-vA8bmGSXo1KakBO8gow8eQ@public.gmane.org wrote in post #972869: > > On Jan 5, 12:10pm, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: > >> syntax, without proprietary extensions. This gives the best portability > >> across databases. > > > > Just tacking on another suggestion to this if people are reading back > > through here: if you do need literal SQL its a good idea to put it in > > a configuration file with a lookup key > > (i.e. :count_all_my_angry_birds); > > Why not just use a named scope (or the Rails 3 equivalent)? That''s what > I tend to do for complex queries. Granted, you don''t get all the SQL in > one file, but that''s a *good* thing: it means you''re looking at the SQL > in context. > > I want to like your config file idea, but I think it''s just reinventing > stored procedures in a way that removes their remaining advantages. > > > that way if you switch db engines or > > support multiple ones all your specific SQL is in one location that > > you can ensure works for whatever different dbs you need to support. > > And of course keep that file as ANSI compliant so that there are as > > little changes required as possible. > > ...in which case your proposed solution isn''t necessary anyway. :) > > > > > \Peter > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > Posted via http://www.ruby-forum.com/. > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.