CODEWITHSUNDEEP
sqlsqlitetree
Ian Vink
Ian Vink

Reputation: 68750

SQL: Tree structure without parent key

Note: The Data schema can not be changed. I'm stuck with it.

Database: SQLite

I have a simple tree structure, without parent keys, that is only 1 level deep. I have simplied the data for clarity:

ID    Content       Title
1     Null          Canada
2     25            Toronto
3     33            Vancouver
4     Null          USA
5     45            New York
6     56            Dallas

The structure is ordinal as well so all Canadian Cities are > Canada's ID of 1 and less than the USA's ID of 4

Question: How do I select all a nation's Cities when I do not know how many there are?

Upvotes: 0

Views: 173

Answers (3)

biziclop
biziclop

Reputation: 14596

My query assigns every city to every country, which is probably not what you want, but:

http://sqlfiddle.com/#!5/94d63/3

SELECT *

FROM (
      SELECT
        place.Title AS country_name,
        place.ID AS id,

        (SELECT MIN(ID)
         FROM place AS next_place
         WHERE next_place.ID > place.ID
           AND next_place.Content IS NULL
        ) AS next_id

      FROM place
      WHERE place.Content IS NULL
     ) AS country

INNER JOIN place
 ON place.ID > country.id
AND CASE WHEN country.next_id IS NOT NULL
         THEN place.ID < country.next_id
         ELSE 1 END

Upvotes: 2

dbenham
dbenham

Reputation: 130839

select * from tbl
 where id > 1
   and id < (select min(id) from tbl where content is null and id > 1)

EDIT

I just realized the above does not work if there are no countries with greater ID. This should fix it.

select * from tbl a
 where id > 4
   and id < (select coalesce(b.id,a.id+1) from tbl b where b.content is null and b.id > a.id)

Edit 2 - Also made subquery fully correlated, so only have to change country id in one place.

Upvotes: 1

Rednaxel
Rednaxel

Reputation: 968

You have here severals things to consider, one is if your data is gonna change and the other one is if it isn't gonna change, for the first one exist 2 solutions, and for the second, just one.

  • If your data is organize as shown in your example, you can do a select top 3, i.e.

    SELECT * FROM CITIES WHERE ID NOT IN (SELECT TOP 3 ID FROM CITIES)

  • You can create another table where you specify wich city belongs to what parent, and make the hierarchy by yourself.

I reccomend the second one to be used.

Upvotes: 0

Related Questions