CODEWITHSUNDEEP
phpmysqlsqlselect
aborted
aborted

Reputation: 4541

SELECT and list children and parent

I need a SQL query for the following COMPLEX task...

I need to to select from a column named parent_id. If a row has 0 for parent_id it means that it's a category (it also has the type column which says cat for category), if a row has 1 or more for parent_id it means that it is a rule. The parent_id of a rule, is the rule_id of a category.

The table structure with some data in it: phpMyAdmin table data

I need to select in a way, that EACH category, has its children under it. From the picture we would get this:

Sample Category 1
   Sample rule 1
Sample Category 2

I tried some some queries that I found from here, but none worked.

This is what I tried last:

'SELECT *
FROM ' . RULES_TABLE . ' AS parent
    LEFT JOIN ' . RULES_TABLE . ' AS child 
    ON child.parent_id = parent.rule_id 
WHERE parent.parent_id = 0
     AND parent.public = 1
ORDER BY parent.cat_position, child.rule_position';

It returned Sample rule 1 only.

Ideas?

Upvotes: 1

Views: 1237

Answers (2)

xception
xception

Reputation: 4287

Try this fiddle http://sqlfiddle.com/#!2/0a9c5/16, sql code below

SELECT c.rule_id, c.rule_title, GROUP_CONCAT(r.rule_title)
FROM RULES_TABLE AS c
LEFT JOIN RULES_TABLE AS r
    ON r.parent_id = c.rule_id
WHERE c.parent_id IS NULL AND c.public = 1
GROUP BY c.rule_id
ORDER BY c.cat_position, c.rule_position;

left out php-ization of code on purpose to maintain syntax highlighting, which seems not to work anyway

If you exceed maximum allowed group_concat size you can either increase it or use the following version, and do a bit more processing in php:

SELECT c.rule_id, c.rule_title, r.rule_title as r_rule_title
FROM RULES_TABLE AS c
LEFT JOIN RULES_TABLE AS r
    ON r.parent_id = c.rule_id
WHERE c.parent_id IS NULL AND c.public = 1
ORDER BY c.cat_position, c.rule_position;

and in php, skeleton code provided only, fill in with the actual values, assuming you use pdo and your pdostatement var is named $query:

$old_rule_id = null;
$rrt = array(); // will contain all r.rule_titles for a give c.rule_id
while( ($i = $query->fetch(PDO::FETCH_OBJ)) !== false ) {
    if( $old_rule_id != $i->rule_id ) {
        if( count($rrt) ) {
            echo ... all the data for previous rule from $rrt ...
            echo ... end of previous element ...
            $rrt = array(); // empty it to collect new data
        }
        echo ... start of current element use data from $i->rule_id and $i->rule_title ...
        $old_rule_id = $rule_id;
    }
    $rrt[] = $i->r_rule_title;
}
// the following is the same if from inside the while, minus reinitialization of $rrt;
if( count($rrt) ) {
    echo ... all the data for previous rule from $rrt ...
    echo ... end of previous element ...
}

replace stuff between ... with valid code

Upvotes: 2

Armando Peña
Armando Peña

Reputation: 433

Using FOR XML EXPLICIT you can get a hierarchical list.

Try this:

SELECT 
 1 Tag,
 null Parent,
 '' [root!1!name],
 null [category!2!id],
 null [category!2!name],
 null [rule!3!id],
 null [rule!3!name]

UNION ALL 

SELECT DISTINCT 
 2 Tag,
 1 Parent,
 null,
 rule_id [category!2!id],
 rule_title [category!2!name],
 null [rule!3!id],
 null [rule!3!name]
FROM rules
WHERE parent_id = 0 and rule_type = 'cat'

UNION ALL 

SELECT  
 3 Tag,
 2 Parent,
 null,
 parent_id [category!2!id],
 null [category!2!name],
 rule_id [rule!3!id],
 rule_title [rule!3!name]
FROM rules
WHERE parent_id > 0 and rule_type = 'rule'

FOR XML EXPLICIT

For more information visit http://msdn.microsoft.com/en-us/library/ms189068.aspx

Upvotes: -1

Related Questions