Hi Mike,
Yes, this is possible, however, it is complicated.
Lets first create the tables an populate them:
create table Container (ContainerName text, ContainerId text primary key)
create table Contents (ContentName text, ContainerId text)
insert into Container values ('Fruit', 'ABC-0001');
insert into Container values ('Vegetable', 'ABC-0002');
insert into Container values ('Computer', 'ABC-0003');
insert into Contents values ('Orange (Navel)', 'ABC-0001');
insert into Contents values ('Apple (Baldwin)', 'ABC-0001');
insert into Contents values ('Apple (Granny Smith)', 'ABC-0001');
insert into Contents values ('Broccoli', 'ABC-0002');
insert into Contents values ('Carrot', 'ABC-0002');
insert into Contents values ('Picture 1.png', 'ABC-0003');
insert into Contents values ('Safari.app', 'ABC-0003');
insert into Contents values ('Portfolio.psd', 'ABC-0003');
insert into Contents values ('iTunes.app', 'ABC-0003');
The following select gets you the desired result. Note that your
condition on the ContentName ( where ContentName like '%app%' ) appeares
twice. I could not find a way to avoid this.
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
)
Martin
Mike Yenco wrote:
> 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
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
