Member Avatar for sherinpoulose

how to populate treeview from multiple columns of sql data table

I have attached my sql table here.from that table,i have to populate a treeview of the below form;

-outlook
---------overcast
-----------------------------yes
---------rainy
----------------wind
----------------------strong
------------------------------no
----------------------weak
-------------------------------yes
---------sunny
---------------humidity
------------------------high
--------------------------------no
-------------------------normal
---------------------------------yes.


It is readable like this;
if outlook is overcast then yes
if outlook is rainy and if wind is strong then no
if outlook is rainy and wind is weak then yes
if outlook is sunny and humidity is high then no
if outlook is sunny and humidity is normal then yes


how can i use this table to populate treeview.

Rule.docx (10.36 KB)
  • 3 Contributors
  • 3 Replies
  • 199 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

As ’s sample shows a decision-path per row (outlook → attribute → answer), there are two practical, easy-to-maintain patterns: (1) keep each row as a path and build the visual tree in the application layer, or (2) normalize nodes as parent/child rows and let the database return a hierarchical result that the UI consumes. Both approaches were hinted at by and ; the choice depends on whether the tree depth is fixed and how large/volatile the data set is.

If using a normalized adjacency list (id, parent_id, label), a recursive query produces depth and a path (MySQL 8.0+). Example:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, label, 0 AS depth,
         CAST(label AS CHAR(1000)) AS path
  FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, n.label, t.depth + 1,
         CONCAT(t.path, '/', n.label)
  FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT id, parent_id, label, depth, path FROM tree ORDER BY path;

The path column is convenient for the UI; for pre-8.0 MySQL do recursion in the client or a stored procedure.

A simple client-side algorithm (C# pseudocode) that inserts nodes level-by-level from a path value:

foreach (row in resultRows) {
  parts = row.path.Split('/');
  cur = treeView.Nodes;
  foreach (part in parts) {
    node = cur.Cast<TreeNode>().FirstOrDefault(n => n.Text == part);
    if (node == null) { node = new TreeNode(part); cur.Add(node); }
    cur = node.Nodes;
  }
}

Store the DB id in the node’s Tag so children can be loaded lazily on Expand.

Notes and gotchas: index parent_id for performance, prevent cycles (FK constraints or application checks), handle identical labels in different branches by using IDs for identity, and use lazy-loading for very large trees.

Recommended Answers

All 3 Replies

Member Avatar for smantscheff

You can do it in a query which uses 3 left joins to connect from the highest to the lower levels of the hierarchy. But this is not a general solution as the hierarchy can - in principle - be arbitrarily deeply nested. Therefore you will need a prodedure to build a general tree view model - either in an external procedural language like PHP or, preferably, in MySQL itself.

commented: agree +13
Member Avatar for sherinpoulose

You can do it in a query which uses 3 left joins to connect from the highest to the lower levels of the hierarchy. But this is not a general solution as the hierarchy can - in principle - be arbitrarily deeply nested. Therefore you will need a prodedure to build a general tree view model - either in an external procedural language like PHP or, preferably, in MySQL itself.

can you please explain sir....I am a beginner in sql...

Member Avatar for debasisdas

As smantscheff mentioned you need to pull the records from DB by using joins between your dependent tables. Better to use some external tool to populate the tree.

Edited by debasisdas because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.