mysql select query missing a row

Question

I have a mysql table like this

+---------+----------------+-------+------------+
| dept_id | parent_dept_id | level | name       |
+---------+----------------+-------+------------+
|       1 |              0 |     0 | Company    |
|       2 |              1 |     1 | HR         |
|       3 |              1 |     1 | Finance    |
|       4 |              1 |     1 | Operations |
|       5 |              4 |     2 | Sales      |
|       6 |              2 |     2 | Training   |
+---------+----------------+-------+------------+

I am trying to retrieve all departments and their parent departments. But what I also want is to retrieve the deprtment with ID 1. This guy is the top department and does not have a parent, all I want is a null in the department name. So far I have this query

select
    d1.dept_id,
    d2.name as parent_name,
    d1.parent_dept_id,
    d1.level,
    d1.name
from
    tdept d1,
    tdept d2
where
    d1.parent_dept_id = d2.dept_id;

Currently, all departments get retrieved except for the top one, how can I achieve this?

@7alhashmi I just want dept_id, parent_name, parent_dept_id, level, name. parent_dept_id and parent_name can be NULL — Crouzilles
– Crouzilles, Commented Sep 26, 2013 at 11:04

Nadeem_MK · Accepted Answer · 2013-09-26 10:52:51Z

4

Try using the LEFT OUTER JOIN (The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.)

select
    d1.dept_id,
    d2.name as parent_name,
    d1.parent_dept_id,
    d1.level,
    d1.name
from tdept d1
Left outer join tdept d2
where  d1.parent_dept_id = d2.dept_id;

answered Sep 26, 2013 at 10:52

Nadeem_MK

7,7217 gold badges53 silver badges62 bronze badges

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

2 Comments

Crouzilles Over a year ago

@Nademm_MK when I do this I get an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where d1.parent_dept_id = d2.dept_id' at line 9' in blahblahblah

Nadeem_MK Over a year ago

If it works for you, you can select it as your answer. Sometimes answers are meant to give hints as we can't really test as we're don't have the same development environment.

Crouzilles · Accepted Answer · 2013-09-26 11:07:56Z

0

Ok, I got it working, I did this:

select
    d1.dept_id,
    d2.name as parent_name,
    d1.parent_dept_id,
    d1.level,
    d1.name
from
    tdept d1 left outer join tdept d2 on d1.parent_dept_id = d2.dept_id;

Thanks Nadeem_MK for putting me in the right direction

Crouz

answered Sep 26, 2013 at 11:07

Crouzilles

8232 gold badges15 silver badges28 bronze badges

Comments

7alhashmi · Accepted Answer · 2013-09-26 11:08:06Z

0

Try something like this:

select
t1.dept_id,
t2.name,
t1.parent_dept_id,
t1.level,
t1.name
from tdept t1
Left outer join tdept t2
ON t1.parent_dept_id = t2.dept_id;

answered Sep 26, 2013 at 11:08

7alhashmi

9247 silver badges26 bronze badges

Collectives™ on Stack Overflow

mysql select query missing a row

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related