On 20 Sep 2013, at 1:52pm, Flakheart <gary.flakhe...@gmail.com> wrote:
> I have two tables with data and I know I can use JOINS to list all data with > duplications in a monolithic table but I need something along the following > lines for user display purposes. > > Groceries table: > > Itemname, Category, Brandname, Manufacturer, Packaging, Units, Weight, Note, > Picture, Barcode, Deleted, Record > > History table: (ItemDataId corresponds to the Record number in Groceries > table) > > ItemDataId, Boughton, Boughtfrom, Quantity, Aisle, Price, Discount, Total, > Note, Record > > 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. In your situation I'd probably do something like (simplified) SELECT * FROM History ORDER BY ItemDataId, Boughton DESC This would give you your entire purchase history sorted so that all the entries for one Grocery are listed together, and within that the most recent purchase is at the top. Then I'd have my program go down the list, printing each line, but before I print a line I compare the ItemDataId in this row with the ItemDataId of the last row. If the ItemDataId has changed, then you know you've moved to a new grocery, so you need to print a header for the new one. So you do SELECT * FROM Groceries WHERE rowid = [the new itemDataId] and that's the data to show in the header. There are many improvements to above (including not fetching * unless you need *) but that should get you started. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
