Re: [sqlite] Help on sql syntax, left join and group_concat
"He Shiming" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> select NAME, group_concat(COUNT), group_concat(TYPE) >> from ( >> select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE >> from T1 left join T2 on T1.ID=T2.REFID >> order by T2.COUNT); >> > Thanks for the hint. It works, but I noticed the query takes much > longer to complete (5 times actually in my smaller testing db). Sorting is not free. > Can it be done without a sub-select? I can't think of a way. > About performance, it is important in my scenario. Isn't it always? > That's why I used > group_concat. I don't quite see how this conclusion follows from premises. > I could use additional queries to fetch the data from > T2. But I figured as long as it's done in a single query instead of > multiple, the performance shall be better. Please correct me if I'm > wrong here, because I'm not so sure if I'm heading in the right > direction. I don't understand what exactly you are trying to achive. In any case, when in doubt, try it both ways and see which one performs better. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help on sql syntax, left join and group_concat
> First, note that left join is a red herring here, since you don't in fact > have any records in T1 without a matching record in T2. > > Try this: > > select NAME, group_concat(COUNT), group_concat(TYPE) > from ( > select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE > from T1 left join T2 on T1.ID=T2.REFID > order by T2.COUNT); > > Igor Tandetnik > > Well, in fact, it's possible that a record in T1 doesn't have a matching record in T2. So I have to use left join. I'm sorry for not clearing that up. Thanks for the hint. It works, but I noticed the query takes much longer to complete (5 times actually in my smaller testing db). My real scenario is much more complicated and the performance is even worse. So, any alternatives? Can it be done without a sub-select? About performance, it is important in my scenario. That's why I used group_concat. I could use additional queries to fetch the data from T2. But I figured as long as it's done in a single query instead of multiple, the performance shall be better. Please correct me if I'm wrong here, because I'm not so sure if I'm heading in the right direction. Thanks, He Shiming ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help on sql syntax, left join and group_concat
"He Shiming" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I need some help on a particular sql statement syntax. Consider the > following tables: > > T1: > ID, NAME > 1, John > > T2: > REFID, COUNT, TYPE > 1, 9, B > 1, 5, U > 1, 8, T > > I have the following statement: > select T1.NAME, group_concat(T2.COUNT), group_concat(T2.TYPE) from T1 > left join T2 on T1.ID=T2.REFID; > > And the result is: > John, 9,5,8, B,U,T > > What I want is to make the join part sorted by T2.COUNT, so that the > result goes: > John, 5,8,9, U,T,B First, note that left join is a red herring here, since you don't in fact have any records in T1 without a matching record in T2. Try this: select NAME, group_concat(COUNT), group_concat(TYPE) from ( select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE from T1 left join T2 on T1.ID=T2.REFID order by T2.COUNT); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
