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? -Mike _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
