CODEWITHSUNDEEP
sqlsql-server
user1315906
user1315906

Reputation: 3504

Selecting values from 2 different tables

I have a table called Doctor.

Doctor table consist of 4 fields. DoctorID,DoctorName, DoctorAddress, DoctorSpeciality

I have another table called PatientData. and it has 4 fields in it. PatientId, PatientName, PatientTelephone, DoctorID.

I need to write a SQL that would display the following fields;

PatientID, PatientName, DoctorName, DoctorSpeciality

1.) I think, i will have to use an INNER JOIN here, but i am not sure how to write it for this scenario. An outer join would also work i guess, but i am new to Joins. Can someone please help me here ?

2.) Can i create a VIEW for the SQL statement that i am creating above ?

Upvotes: 1

Views: 77

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Something like this should would using a regular INNER JOIN -- this will return all records from the Doctor table with a matching record in the PatientData table:

SELECT PD.PatientId, PD.PatientName, D.DoctorName, D.DoctorSpecialty
FROM Doctor D
   INNER JOIN PatientData PD ON D.DoctorId = PD.DoctorId

If you want to return all data from one of the other tables, look into using a OUTER JOIN (I prefer LEFT JOINs).

Here's a nice article on visual representation of joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

And yes, you can create a view if you'd like -- depends on your needs. Something like this should be close:

CREATE VIEW DoctorPatients AS
SELECT PD.PatientId, PD.PatientName, D.DoctorName, D.DoctorSpecialty
FROM Doctor D
   INNER JOIN PatientData PD ON D.DoctorId = PD.DoctorId

SQL Server Views: http://msdn.microsoft.com/en-us/library/ms187956.aspx

Upvotes: 2

Steve
Steve

Reputation: 216353

It is a simple join

SELECT p.PatientID, p.PatientName, d.DoctorName, d.DoctorSpeciality
FROM PatientData p JOIN Doctor d on d.DoctorID = p.PatientID

Of course you could create a view from this

   CREATE VIEW [dbo].[PatientAndDoctor]
   AS
      SELECT p.PatientID, p.PatientName, d.DoctorName, d.DoctorSpeciality
      FROM PatientData p JOIN Doctor d on d.DoctorID = p.PatientID

Upvotes: 1

Related Questions