How to get result when the row of joined table doesn't exist?

JeffinAtl 161 Reputation points
2021-02-18T04:01:43.133+00:00

I have a query like following. SELECT P.PatNum, P.Title, P.FName, P.LName, P.HmPhone, P.WkPhone, P.Birthdate, P.Address, P.Address2, P.City, P.State, P.Zip, P.Gender, P.SSN, P.MedUrgNote, P.PatStatus, P.Guarantor, P.Position, P.MiddleI, P.ChartNumber, P.WirelessPhone, P.TxtMsgOK, R.DateDue, R.DatePrevious, R.IsDisabled FROM patient AS P INNER JOIN recall AS R ON P.PatNum = R.PatNum WHERE P.PatNum = 3 This query works well when the row of joined table(recall) exists. However I can't get when the row of joined table(recall) doesn't exist. I want to get the result even when the row of joined table(recall) doesn't exist.(In this case patient table's fields should be acquired and recall table's fields should be null. How to do this?

{count} votes

1 answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,136 Reputation points
    2021-02-18T05:39:07.3+00:00

    Hi @JeffinAtl ,

    Welcome to Microsoft Q&A!

    For this type of problem we recommend that you post CREATE TABLE statements for your tables(patient and recall) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    You could also refer below query using Left join instead of inner join and check whether it is working or not.

    SELECT P.PatNum, P.Title, P.FName, P.LName, P.HmPhone, P.WkPhone, P.Birthdate,   
    P.Address, P.Address2, P.City, P.State, P.Zip, P.Gender, P.SSN, P.MedUrgNote,   
    P.PatStatus, P.Guarantor, P.Position, P.MiddleI, P.ChartNumber, P.WirelessPhone,   
    P.TxtMsgOK, R.DateDue, R.DatePrevious, R.IsDisabled   
    FROM patient AS P   
    LEFT JOIN recall AS R   
    ON P.PatNum = R.PatNum   
    WHERE P.PatNum = 3  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments