On Sat, 21 Sep 2013 15:06:14 +0100 Simon Slavin <slav...@bigfraud.org> wrote:
> > If I "SELECT * FROM Groceries" to list all records, how do I > > arrange a query to list each record in the groceries table with all > > matching history items under each record. > > You do it with two queries, not one. In all seriousness, layout is > the job of your programming language. The database system just hands > it data. Don't try to use SQL to do your layout for you, you'll end > up with a huge SELECT statement that is difficult to understand and > breaks any time you change anything. Yes, SQL is not a layout/display language. But that's not very good support for the advice to use two queries. Joining two tables is hardly going to create one "huge" query. More important, in the general case two queries cannot be guaranteed to be consistent: it is possible for a grocery item to have been deleted while processing the history table. And then there's performance. Issuing per-row queries while processing a results must be the dominant antipattern of our time. Instead of a single query and one trip to the DBMS, the application endures N trips, with the attendant delay. The right answer is to join the tables in a single query and -- as you suggest --use application logic detect when a "new" item needs a new header. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
