none
Self Join does not work with NULL RRS feed

  • Question

  • Hi all

    I've inherited a DB running on SQL 2008 R2. I have an Employees table  with this structure (I miss some not crucial fields):

    ID  SURNAME   NAME  IDPREV  IDNEXT

    Logic is pretty straightforward. People change on a particular duty frquently. So a guy can have a previous colleague and, when a new one comes, a "next" colleague. Problem is that in the past IDPREV and IDNEXT fields haven't been filled up properly: someone has his IDPREV but not his IDNEXT, someone has nothing at all and someone ha both fields filled up.

    I read about self join but all I get is a query partially working. I can show in a web page the current guy andhis related IDPREV but if there's no IDPREV (i.e. a NULL value) I have no data at all. I have to solve this first in order to get a correct representation of both IDPREV and IDNEXT even if they are both NULL.

    Here what I was able to write:

    "SELECT DISTINCT dbo.Employees.ID, dbo.Employees.SURNAME, dbo.Employees.NAME, dbo.Employees.IDPREV, MIRROR.ID, MIRROR.SURNAME + MIRROR.NAME AS PPREVIOUS, 
    FROM dbo.Employees, dbo.Employees MIRROR 
    WHERE dbo.Employees.ID = " + param + " AND dbo.Employees.IDPREV = MIRROR.ID" );
    
    param is the ID passed  from a webpage appended in ?param=nnnn format.

    Any suggestion and/or advice are more than welcome.

    Thanks in advance.

    Nicholas

    Wednesday, June 12, 2019 9:05 PM

Answers

  • Don't use old-style joins via the FROM clause. You need a left join to retrieve all rows that match your argument and the associated "previous" row (if any). Something like:

    select .... 
    from dbo.Employees as emp 
    left join dbo.Employees as prevemp 
    on emp.IDPREV = prevemp.ID
    order by ...;

    Note I left out distinct. Perhaps you do need it because your table has no proper constraints. Often that is used when the query has a logic mistake or the schema is faulty; don't use it if you don't need it. And if order of rows in the resultset matters, then you must include an order by clause to guarantee that order. Without it the engine is free to return rows in any order and eventually it will - despite any pattern you might see. 

    Wednesday, June 12, 2019 9:25 PM

All replies

  • Don't use old-style joins via the FROM clause. You need a left join to retrieve all rows that match your argument and the associated "previous" row (if any). Something like:

    select .... 
    from dbo.Employees as emp 
    left join dbo.Employees as prevemp 
    on emp.IDPREV = prevemp.ID
    order by ...;

    Note I left out distinct. Perhaps you do need it because your table has no proper constraints. Often that is used when the query has a logic mistake or the schema is faulty; don't use it if you don't need it. And if order of rows in the resultset matters, then you must include an order by clause to guarantee that order. Without it the engine is free to return rows in any order and eventually it will - despite any pattern you might see. 

    Wednesday, June 12, 2019 9:25 PM
  • Thank you so much for your reply. I fixed the IDPREV part and it works well now.

    Is there a "simple" way like that to include also the IDNEXT?

    Nicholas

    Wednesday, June 12, 2019 10:18 PM
  • Hi Nicholas__M,

     

    We are glad to hear that  you have solved your issue. If you would like a right solution, please share us your table structure and some simple data so that we will get a right direction and make some test.

     

    Would you like following script.

    select .... 
    from dbo.Employees as emp 
    left join dbo.Employees as prevemp on emp.IDPREV = prevemp.ID
    left join dbo.Employees as nextemp on emp.IDNEXT = nextemp.ID
    order by ...;


    By the way, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 13, 2019 2:18 AM
  • Thank you so much.

    Your new row is real very simple way: my T-SQL ignorance do the rest.

    I've my complete issue solved.

    Do I have to mark this reply as answer too?

    Nicholas.

    Friday, June 14, 2019 8:14 PM