CODEWITHSUNDEEP
sqlsqlite
user3599629
user3599629

Reputation: 227

select statement for specific value sqlite

could you help me to make a select query for this case,

recently i'm looking for a way to implement expandable list view that fill the data from database, but i'm not found a proper example yet, and this i'm thinking about another way,

i have 2 table :

table1 :

   +------------+----------+
   | id_table1  |  Item    | 
   +------------+----------+
   | 1          | Item1    |
   | 2          | Item2    |
   | 3          | Item3    |
   | 4          | Item2.1  |
   | 5          | Item2.2  |
   | 6          | Item3.1  |
   | 7          | Item3.2  |
   +------------+----------+

table 2 : id_table2.table2 = id_table1.table1 and table2.id_table = id_table1.table1

   +------------+----------+
   | id_table2  | id_table | 
   +------------+----------+
   | 2          | 4        |
   | 2          | 5        |
   | 3          | 6        |
   | 3          | 7        |
   +------------+----------+

and with some select query the result will be :

   Item1
   Item2
     Item2.1    //with space
     Item2.2    //with space
   Item3
     Item3.1    //with space
     Item3.2    //with space

Upvotes: 0

Views: 47

Answers (1)

wwkudu
wwkudu

Reputation: 2796

You can do what you want to do with these tables, a la the following:

select id_table1, Item from table1
where not exists (
  select id_table2
  from table2 where id_table1=id_table)
union
select id_table2, child.Item 
from table1 parent, table2, table1 child
where table2.id_table2=parent.id_table1
  and table2.id_table=child.id_table1;

The first query finds those items that are "parent" items. The second one finds those that are children. (You might have some issues ordering later on. And this assumes only two levels at the moment.) But it is not a very clear way to do it. At least I would suggest column names that indicate what you are doing, e.g:

table1: ViewItem. Columns: id, Item
table2: ItemChild. Columns: parentId, childId

You will find quite a few hits on this type of question, hierarchical menus being one such application.

Upvotes: 2

Related Questions