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
2
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
1
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
2
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
1
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