Use a table multiple times in a SELECT statement

I'm making the following SQL call:

SELECT dpt_id, 
dpt_title, 
dpt_active, 
dpt_parent
FROM tbl_department ORDER BY dpt_title

where dpt_parent is an integer value of a parent department id (the dpt_id of another department).

I'd like the result set of the above call to include the dpt_title of that dpt_parent. My initial attempt at this was as follows:

SELECT dpt_id, 
dpt_title, 
dpt_active, 
dpt_parent, 
    (SELECT dpt_title 
     FROM tbl_department 
     WHERE tbl_department.dpt_id = dpt_parent
     ) AS parent_title
FROM tbl_department ORDER BY dpt_title

With 开发者_运维百科the call in this form, all values of parent_title are NULL I can see using the same table like this is confusing, what is the solution to this problem?

use a table alias:

SELECT dpt_id, 
x.dpt_title, 
x.dpt_active, 
x.dpt_parent, 
    (SELECT dpt_title 
     FROM tbl_department 
     WHERE tbl_department.dpt_id = x.dpt_parent
     ) AS parent_title
FROM tbl_department x ORDER BY dpt_title

but you'd be better off removing the sub select and just joining like:

SELECT 
     a.dpt_id
    ,a.dpt_title
    ,a.dpt_active
    ,a.dpt_parent
    ,b.dpt_title parent_title
FROM tbl_department      a
LEFT JOIN tbl_department b ON a.dpt_parent = b.dpt_id
ORDER BY a.dpt_title, b.dpt_title

SELECT d.dpt_id, 
d.dpt_title, 
d.dpt_active, 
d.dpt_parent,
p.dpt_title parent_title
FROM tbl_department d
LEFT JOIN tbl_department p ON d.dpt_parent = p.dpt_id
ORDER BY d.dpt_title

SELECT d.dpt_id, 
d.dpt_title, 
d.dpt_active, 
d.dpt_parent,
p.dpt_title AS parent_title
FROM tbl_department d
LEFT JOIN tbl_department p
    ON p.dpt_parent = d.dpt_id
ORDER BY d.dpt_title

It looks to me this query needs to be rewritten as:

SELECT dpt_id,  
dpt_title,  
dpt_active,  
dpt_parent,  
    (SELECT dpt_title  
     FROM tbl_department AS [dept2]
     WHERE [dept2].dpt_id = [dept1].dpt_parent 
     ) AS parent_title 
FROM tbl_department AS [dept1] ORDER BY dpt_title 

Otherwise SQL-server will not know with instance of the table you are talking about

i think you're looking for something like that:

SELECT a.dpt_id, 
a.dpt_title, 
a.dpt_active, 
a.dpt_parent, 
b.dpt_title AS parent_title
FROM tbl_department AS a
INNER JOIN tbl_department AS b ON b.dpt_id = a.dpt_parent
ORDER BY dpt_title

I think the solution is to make a join on the same table or you can make a user defined function that takes the department id and return the parent department name.