Sql query is returning same rows

Hey Guys i want to thank you again for all the support you rendered it make my coding skill improve and i keep learning more and more.

I have a challenge i am trying to retrieve patient record when the patient enrollement ID is found from a View called "HIVENCOUNTER" let me show it with Table

tbl_HIVENCOUNTER

Ptn_pk	PatientID	Drug	Duration	Wieght	VisitID	VisitDate
00245	101001	ART	30	140	111	10/01/2001
00245	101001	ART	60	100	122	05/02/2001
00245	101001	ART	30	110	212	03/03/2001
00246	101002	ART	90	120	121	12/01/2001
00246	101002	ART	30	150	1414	06/02/2001
00246	101002	ART	120	60	1415	10/03/2001

Based on the above table i want to retrieve record as follows :

select * from tbl_HIVENCOUNTER t

Inner Join mst_patient p on t.Ptn_pk = p.Ptn_pk and p.PatientID = '101001'

order by t.VisitDate Asc

it return just two record instead of three as following :

Ptn_pk	PatientID	Drug	Duration	Wieght	VisitID	VisitDate
00245	101001	ART	30	140	111	10/01/2001
00245	101001	ART	60	100	122	05/02/2001

i also modify the script like this

Select * from tbl_HIVECOUNTER t where t.Ptn_pk = (Select ptn_pk from mst_patient where PatientID = t.PatientID) order by t.VisitDate

this also is returning the same result, i am implementing this code with C# loop "While loop"

Note : mst_Patient is the master patient table that contain the demographic record of each Patient

any suggestion will be appreciated. Thank you all

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar9y ago
Could you please share data from table mst_patient against patient id=101001
Bikesh Srivastava9y ago
Hi,
you can find solution from this link.
http://stackoverflow.com/questions/36555265/asp-net-mvc-core-6-multiple-submit-buttons

Mark as answer if you will find answer of your query..
Tapan Patel9y ago
Is it c# that is listing records as 2 or sql server returning 2 rows ? I believe there is nothing wrong with the query but it would be great to check for the number of records in your view one more time.

I believe your master patient table mst_patient will have only one row pointing to the patient#245, and as you are using inner join, it is limiting the records.