Mysql join query on same table

Question

I have table like

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `status` enum('1','2') NOT NULL COMMENT '''1''=active,''2''=inactive',
  PRIMARY KEY (`id`)
)

now here I want records like

id,title,parent_title,status

means I want relative title in place of parent_id(at here parent_title)

medina · Accepted Answer · 2014-02-11 00:35:09Z

2

Here it is mate:

SELECT c1.id, c1.title, c2.title as parent_title, c1.status
FROM categories c1
LEFT JOIN categories c2 ON (c2.id = c1.parent_id)

edited Feb 11, 2014 at 0:35

user142162

answered Jun 14, 2013 at 6:25

medina

8,2294 gold badges28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2353802 Over a year ago

have you any idea how to do same things in cakephp?

medina Over a year ago

Hey mate, sorry I don't work with cake. However you should be able to join the same entity in your Entity Manager. =)

Er.KT · Accepted Answer · 2013-06-14 06:52:12Z

1

$fields = array('c1.id, c1.title, c2.title as parent_title, c1.status');
        $joins = array();
            $joins[] = array(
                'table' => 'categories',
                'foreignKey' => false,
                'conditions' => 'c2.id = c1.parent_id',
                'type' => 'LEFT',
                'alias' => 'c2',
            );

$this->paginate = compact('fields', 'joins');

answered Jun 14, 2013 at 6:52

Er.KT

2,8702 gold badges39 silver badges73 bronze badges

Comments

Goutam Pal · Accepted Answer · 2013-06-14 06:55:53Z

1

Cakephp code

In Category Controller

$fields=array(
            'Category.*',
            'ParentCategory.title'
            );
$data = $this->Category->find("all",array(
                'fields' => $fields,
                'recursive'=>0
            ));

In category Model

var $belongsTo = array(

        'ParentCategory' => array(
            'className' => 'Category',
            'foreignKey' => 'parent_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
);

answered Jun 14, 2013 at 6:55

Goutam Pal

1,7531 gold badge10 silver badges14 bronze badges

2 Comments

Er.KT Over a year ago

in this case I need to create one more model 'ParentCategory'?

Er.KT Over a year ago

also I need to include this model in my controller ?

Collectives™ on Stack Overflow

Mysql join query on same table

3 Answers 3

2 Comments

Comments

Cakephp code

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Cakephp code

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related