从2个不同的表中选择值

I have a table called Doctor.

我有一张叫医生的桌子。

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

医生表由4个字段组成。 DoctorID,DoctorName,DoctorAddress,DoctorSpeciality

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

我有另一个名为PatientData的表。它有4个字段。 PatientId,PatientName,PatientTelephone,DoctorID。

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

我需要编写一个显示以下字段的SQL;

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 ?

1.)我想,我将不得不在这里使用INNER JOIN,但我不确定如何为此场景编写它。外联接也可以工作,但我是Joins的新手。有人可以帮我吗?

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

2.)我可以为我上面创建的SQL语句创建一个VIEW吗?

2 个解决方案

#1

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:

这样的事情应该使用常规的INNER JOIN - 这将返回Doctor表中的所有记录以及PatientData表中的匹配记录:

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).

如果要从其他表中返回所有数据,请查看使用OUTER JOIN(我更喜欢LEFT JOIN)。

Here's a nice article on visual representation of joins: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-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

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

#2

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

#1