Query with union condition

Question

Does anyone know how can I write a query to get the desired result to be like tools/lift planning/cranimax from this database

resource_category_id    category_name   parent_category     
    1                            Tools                  0    
    2                    product literature             0   
    3                   Terms and Conitions             0   
    4                        crane library              1   
    5                         geniune                   1   
    6                       lift planning               1   
    8                         cranimax                  6

For this I wrote a query like this which is not completed, for example this is thing am having in my mind if I choose from cranimax it's having a parent_category 6 so it should make union with that row having resource_category_id and that row having parent_category 1 it should repeat until parent category 0 occurs.

$data['breadcrumbs'] = $this->Manito_model->get_breadcrumbs_details($resource_id);

public function get_breadcrumbs_details($resource_id)
{
    $query=$this->db->query("select * from resource_category as m WHERE m.resource_category_id = $resource_id union (select * from resource_category where parent_category != 0) as m2 on m.parent_category = m2.resource_parent_category"); 


    return $query->result();


}

i expect my result to be like this

array (size=3)
 0 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '8' (length=1)
    public 'category_name' => string 'cranimax' (length=8)
    public 'parent_category' => string '6' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)
 1 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '6' (length=1)
    public 'category_name' => string 'lift planning' (length=8)
    public 'parent_category' => string '1' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)

 2 => 
   object(stdClass)[35]
    public 'resource_category_id' => string '1' (length=1)
    public 'category_name' => string 'Tools' (length=8)
    public 'parent_category' => string '0' (length=1)
    public 'created_at' => string '2017-11-04 13:59:39' (length=19)

Do you require tree structure? [this](https://stackoverflow.com/questions/5688376/mysql-select-tree-parent-ids) may guide you. — jagad89, Nov 08 '17 at 11:24
@jagad89 how can we fetch data of only `resource_category_id=` `8` — user_777, Nov 08 '17 at 12:08

score 0 · Answer 1 · answered Nov 08 '17 at 11:13

0

UNION is used to combine the result from multiple SELECT statements into a single result set (ie making it extremely simple: you need to do two select, but you want only one result, then you make a union) In you situation you don't need a UNION, but a simple JOIN which "complete" your results with other data:

select m.*, m2.category_name AS parent_name FROM resource_category AS m LEFT JOIN resource_category m2 ON m2.resource_category_id = m.resource_parent_category WHERE m.resource_category_id = $resource_id

answered Nov 08 '17 at 11:13

Roberto Bisello

1,235
10
18

when i did your code i got like this `public 'resource_category_id' => string '8' (length=1) public 'category_name' => string 'cranimax' (length=8) public 'parent_category' => string '6' (length=1) public 'created_at' => string '2017-11-04 13:59:39' (length=19) public 'parent_name' => string 'lift planning' (length=13)` here its parent_name is getting but i want to repeat it until the parent_category becomes 0 – user_777 Nov 08 '17 at 11:20
recursively? as far as I know it's not possible in mysql (except making functions or similar). But in any case it's not a good practice, because in large database you'll make a really heavy query and the risk of loop is really high... I suggest you to do that kind of operation in php – Roberto Bisello Nov 08 '17 at 11:29

Query with union condition

1 Answers1