loading Data From multiple Tables using inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

loading Data From multiple Tables using inner join

Author

Topic

NightCoder
Starting Member

2 Posts

Posted - 2010-04-04 : 11:42:23

I have a Database with some tables in it. This Database use is to monitor patients and doctors appointments.

I am using a query to get patients and doctors name and surname and Appointment Id from the appointment table. but it is not returning all the data rows. some are skipped.

Query:

SELECT Appointment.AppointmentId, Doctor.Name AS [Doc Name], Doctor.Surname AS [Doc Surname], Patient.Surname AS [Patient Surname], Patient.Name AS [Patient Name]
FROM Appointment INNER JOIN
Doctor ON Appointment.DoctorId = Doctor.ID INNER JOIN
Patient ON Appointment.PatientId = Patient.Id AND Doctor.ID = Patient.PrefferedDoctor

Returned Data:
AppId Doc Name Doc Surname Patient Surname Patient Name
0 Richard Doneo Sejchel Rita
1 Richard Doneo Tabone Daniel
4 Duncan Zarb Bajada Chris
7 Richard Doneo Sejchel Rita
10 Gregory House Schuereb Richard
11 Gregory House Schuereb Richard

Expected Data:

0 Richard Doneo Sejchel Rita
1 Richard Doneo Tabone Daniel
3 Maria Cini Bajada Chris
4 Duncan Zarb Bajada Chris
6 Duncan Zarb Tabone Daniel
7 Richard Doneo Sejchel Rita
8 Duncan Zarb Tabone Daniel
9 Duncan Zarb Tabone Daniel
10 Gregory House Schuereb Richard
11 Gregory House Schuereb Richard
12 Gregory House Bajada Chris

If anyone can help me or know what to do to get the fool data

///|||\\\

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 13:56:12

may be because all doctors are not preffered doctors for patients


SELECT Appointment.AppointmentId, Doctor.Name AS [Doc Name], Doctor.Surname AS [Doc Surname], Patient.Surname AS [Patient Surname], Patient.Name AS [Patient Name]
FROM Appointment INNER JOIN
Doctor ON Appointment.DoctorId = Doctor.ID LEFT JOIN
Patient ON Appointment.PatientId = Patient.Id AND Doctor.ID = Patient.PrefferedDoctor

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NightCoder
Starting Member

2 Posts

Posted - 2010-04-04 : 14:53:40

WOW thanks a lot.
I can't believe how it never occurred to me

///|||\\\

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 05:36:24

Welcome

Thats what you call experience

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources