On Sun, Mar 1, 2009 at 4:04 PM, Mike Yenco <m...@yenco.com> wrote: > > On Mar 1, 2009, at 2:15 PM, Martin Engelschalk wrote: > >> Hi Mike, >> >> Yes, this is possible, however, it is complicated. > > Hi Martin, > > Thanks for the reply. It usually is complicated :-) > > >> select Name, case OrderValue when 0 then NULL else ContainerId end as >> ContainerId >> from >> ( >> select distinct ContainerName as Name, Container.ContainerId as >> ContainerId, 0 as OrderValue >> from Contents join Container on Container.ContainerId = >> Contents.ContainerId >> where ContentName like '%app%' >> union >> select ContentName as Name, Contents.ContainerId as ContainerId, 1 as >> OrderValue >> from Contents join Container on Container.ContainerId = >> Contents.ContainerId >> where ContentName like '%app%' >> order by 2,3 >> ) > > I had simplified my example a bit, but figured out enough of what you > did here to replace the example data with some of the data in my > project to see that it works (fantastic). The only thing now is, I > was hoping by going with a simpler example I would be able to > understand what was suggested. But as you say... it is complicated. > > Is there any chance you can break down what is taking place here into > english so I can maybe get a better sense of what this is doing? > > For example, I'm trying to figure this out so I could maybe adjust it > in some ways such as: > > - returning more columns from Contents > - sorting the results so the Containers are in alphabetical order > followed by the Contents of each in alphabetical order. > - maybe getting a total count of the number of Contents returned > - expanding the search to include multiple columns and criteria. > > I know how to do the above things in rather simple SQLite syntax, but > given the complexity of this I'm not sure where to add such syntax or > if it is even possible in such a structure to add such syntax. Any > pointers that might get me going in the right direction?
work from inside out. Martin's solution has three queries (three SELECT statements). Run them individually from inside out. So -- query1a: SELECT DISTINCT ContainerName AS Name, Container.ContainerId AS ContainerId, 0 AS OrderValue FROM Contents JOIN Container ON Container.ContainerId = Contents.ContainerId WHERE ContentName like '%app%' query1b: SELECT ContentName AS Name, Contents.ContainerId AS ContainerId, 1 AS OrderValue FROM Contents JOIN Container ON Container.ContainerId = Contents.ContainerId WHERE ContentName like '%app%' The two queries return a set of rows each. You UNION the two together to give you a larger set, that is, a set with both sets of rows, ORDERed by 2nd and 3rd columns in the set. Then you query that set with query2: SELECT Name, CASE OrderValue WHEN 0 THEN NULL ELSE ContainerId END AS ContainerId FROM (the set from UNION above) It is going to get even more complicated and soon approach Igor-level of complexity. So, for sanity's sake, it would probably be done better in your application. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
