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

Question

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?

Answer 1

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.