OK, let's see if I can clarify this. I'm using "blank", "Group", and "Code" as placeholders here. Blank is a column that contains nothing (An empty placeholder I need for display purposes). Group could be any text. Code is some ID text.
Table B is being written to by some third-party code which I can't modify. Ideally I would put the "blank" I want into Table B, but unfortunately the third-party code can't cope with that. If it helps, let me fill in some example data: Table A ... let's call it "Container" "" | "Fruit" | "ABC-0001" "" | "Vegetable" | "ABC-0002" "" | "Computer" | "ABC-0003" Table B ... let's call it "Contents" "Orange (Navel)" | "ABC-0001" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Broccoli" | "ABC-0002" "Carrot" | "ABC-0002" "Picture 1.png" | "ABC-0003" "Safari.app" | "ABC-0003" "Portfolio.psd" | "ABC-0003" "iTunes.app" | "ABC-0004" Now let's say I'm looking for as my search anything that contains "app" in the first column of table B. I'm looking for results that group the matching items. "Fruit" "" | "Apple (Baldwin)" | "ABC-0001" "" | "Apple (Granny Smith)" | "ABC-0001" "Computer" "" | "Safari.app" | "ABC-0003" "" | "iTunes.app" | "ABC-003" The rest do not contain "app". There is no need to display the "Vegetable" container as there were no contents found for that one. My first inclination was to go with a repeat loop... take the number of lines in Table A and loop through each one doing a search of contents for my search term... and if anything is returned, displaying the line from Table A followed by any matches. But ideally it seems there should be some way to do this with a single call to SQLite. If grabbing a blank from table A to put before each line of table B is too difficult then... I can probably work around that. But I would still need a way to get the following at a minimum: "Fruit" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Computer" "Safari.app" | "ABC-0003" "iTunes.app" | "ABC-0003" If the blank has to be duplicated to make an equal number of items returned that would also be fine: "Fruit" | "" "Apple (Baldwin)" | "ABC-0001" "Apple (Granny Smith)" | "ABC-0001" "Computer" | "" "Safari.app" | "ABC-0003" "iTunes.app" | "ABC-0003" Possible? -Mike On Mar 1, 2009, at 12:29 PM, Martin Engelschalk wrote: > Hi Mike, > > sorry, i don't fully get it. However, a single SQL statement can not > return data rows of different structure as you indicated in your > example. > Especially, I am confused about "blank". It seems to come from Table > A, > same as "Group *". Do you want to put "Group *" as a sort of headline, > but not "blank" ? > > Martin _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
