CODEWITHSUNDEEP
mysql
user7282
user7282

Reputation: 5196

Get maximum id if there are parent id otherwise get all results

I have a table and having the following data 

CREATE TABLE IF NOT EXISTS `tbl_ticket` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `ticket_title` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `tbl_ticket`
--

INSERT INTO `tbl_ticket` (`id`, `parent_id`, `ticket_title`) VALUES
(1, 0, 'tyty'),
(2, 0, 'testing'),
(3, 0, 'test from ticket'),
(4, 0, 'test ticket'),
(5, 0, 'test ticket'),
(6, 0, 'test ticket'),
(7, 0, 'test ticket'),
(8, 5, 'test ticket'),
(9, 0, '1 Ticket'),
(10, 0, '2Ticket'),
(11, 2, 'ticket2'),
(12, 2, 'ticket1'),
(13, 0, 'title 1234'),
(14, 0, 'titles 1234'),
(15, 14, 'sample 1234');

I need to return all rows where id is not present in parent id from the table. Also if id is present in the parent_id column, I want to get the row having the highest id which matches the parent_id. i.e. I need to return rows with id 1, 3,4,6,7,8,9,10, 12,13, 15.

I tried this sql 

SELECT  `id` ,  `parent_id` 
FROM  `tbl_ticket` 
WHERE id NOT 
IN (

SELECT parent_id
FROM tbl_ticket
)

but it returns value 11 also, instead it should return 12 which is the row having highest id with parent_id =2

Upvotes: 0

Views: 212

Answers (2)

pala_
pala_

Reputation: 9010

Assuming the 5 in your expected output is a typo, as 5 appears in the parent_id field for id=8, you can get your result by the union of two simple queries.

select t1.id
  from tbl_ticket t1
    where not exists (
      select 1 from tbl_ticket
        where parent_id = t1.id
    )
    and parent_id = 0
union all
  select max(id)
    from tbl_ticket
    where parent_id <> 0
    group by parent_id
order by id asc

Fiddle here

The query is in two parts. the first part gets all the tickets that are not present in another tickets parent_id field, and which themselves do not have a parent (parent_id = 0).

The second part of the query looks at those tickets that DO have a parent (parent_id <> 0), and for each group of tickets that share the same parent_id, selects the one with the max id.

The results are then combined with a union to give a single result set. Since the two result sets are mutually exclusive, we can use union all to skip over the duplicate check.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you can do this with not exists rather than combining two separate queries. The advantage is that no duplicate elimination is needed (as is needed when you use union):

select t.*
from tbl_ticket t
where not exists (select 1
                  from tbl_ticket t2
                  where t2.parent_id = t.id
                 ) or
      not exists (select 1
                  from tbl_ticket t2
                  where t2.parent_id = t.id and t2.id > t.id
                 );

The first gets all rows that have no parents. The second gets all rows with the maximum id for a parent.

For best performance, you want an index on tbl_ticket(parent_id, id).

Upvotes: 0

Related Questions