如果要连接的列为空，连接仍然有效吗？

发布于 2024-09-09 07:48:44 字数 935 浏览 2 评论 0原文

我有这个存储过程，当我添加联接时，我没有得到任何结果。这是因为用于连接条件的列 ForClientID 为空吗？这会影响表的结果吗？如果我选择带有 Appointment.ForClientID 而不是 Client.Name 的表，则表的其余部分会加载 ForClientID 列为空的情况。预先感谢，这是一个非常有帮助的社区。

Create procedure GetAppointmentsByProfessionalName(@ProfessionalName varchar(256))
as
    declare @ProfessionalID uniqueidentifier
    set @ProfessionalID = (select UserId from aspnet_Users where UserName = @ProfessionalName)

    select  Appointment.AppointmentID as 'Appointment ID', 
            Client.Name as 'Client Name',
            CONVERT(VARCHAR(10),Appointment.ProposedDate,111) as 'Date', 
            CONVERT(CHAR(8), Appointment.ProposedTime, 114)as  'Time', 
            Appointment.ClientDescription as 'Client Notes', 
            Appointment.Confirmed as 'Confirmed Appointment'
    from Appointment join Client on Client.ClientID = Appointment.ForClientID
    where Appointment.ForProfessionalID = @ProfessionalID
go

原文

I've got this stored procedure, and when I add the join I get no results. Is this because the column used for the join condition, ForClientID, is null? Would that affect the results of the table? If i select the table with Appointment.ForClientID instead of Client.Name, the rest of the table loads with the ForClientID column being null. Thanks in advance, this is an amazingly helpful community.

Create procedure GetAppointmentsByProfessionalName(@ProfessionalName varchar(256))
as
    declare @ProfessionalID uniqueidentifier
    set @ProfessionalID = (select UserId from aspnet_Users where UserName = @ProfessionalName)

    select  Appointment.AppointmentID as 'Appointment ID', 
            Client.Name as 'Client Name',
            CONVERT(VARCHAR(10),Appointment.ProposedDate,111) as 'Date', 
            CONVERT(CHAR(8), Appointment.ProposedTime, 114)as  'Time', 
            Appointment.ClientDescription as 'Client Notes', 
            Appointment.Confirmed as 'Confirmed Appointment'
    from Appointment join Client on Client.ClientID = Appointment.ForClientID
    where Appointment.ForProfessionalID = @ProfessionalID
go

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

む无字情书 2024-09-16 07:48:44

围绕 NULL 的行为是不同类型 JOIN 之间的主要区别。阅读 http://en.wikipedia.org/wiki/Join_%28SQL%29 ，虽然是维基百科，但它是关于该主题的令人惊讶的好文章。

回复收藏 0 原文

相思故 2024-09-16 07:48:44

这是使用 OUTER JOIN 进行重写，以考虑 Appointment.ForClientID 为 NULL 并替换局部变量 @ProfessionalID 与 INNER JOIN：

CREATE PROCEDURE GetAppointmentsByProfessionalName
@ProfessionalName VARCHAR(256)
AS
BEGIN

   SELECT Appointment.AppointmentID AS 'Appointment ID', 
          COALESCE(Client.Name, '{{NO CLIENT}}') AS 'Client NAME',
          CONVERT(VARCHAR(10),Appointment.ProposedDate,111) AS 'Date', 
          CONVERT(CHAR(8), Appointment.ProposedTime, 114)AS  'Time', 
          Appointment.ClientDescription AS 'Client Notes', 
          Appointment.Confirmed AS 'Confirmed Appointment'
     FROM Appointment 
          LEFT OUTER JOIN Client 
             ON Client.ClientID = Appointment.ForClientID
          INNER JOIN aspnet_Users
             ON aspnet_Users.UserId = Appointment.ForProfessionalID
    WHERE aspnet_UsersUserName = @ProfessionalName;

END;

Here's a re-write using an OUTER JOIN to take account of the Appointment.ForClientID being NULL-able and replacing the local variable @ProfessionalID with an INNER JOIN:

CREATE PROCEDURE GetAppointmentsByProfessionalName
@ProfessionalName VARCHAR(256)
AS
BEGIN

   SELECT Appointment.AppointmentID AS 'Appointment ID', 
          COALESCE(Client.Name, '{{NO CLIENT}}') AS 'Client NAME',
          CONVERT(VARCHAR(10),Appointment.ProposedDate,111) AS 'Date', 
          CONVERT(CHAR(8), Appointment.ProposedTime, 114)AS  'Time', 
          Appointment.ClientDescription AS 'Client Notes', 
          Appointment.Confirmed AS 'Confirmed Appointment'
     FROM Appointment 
          LEFT OUTER JOIN Client 
             ON Client.ClientID = Appointment.ForClientID
          INNER JOIN aspnet_Users
             ON aspnet_Users.UserId = Appointment.ForProfessionalID
    WHERE aspnet_UsersUserName = @ProfessionalName;

END;

回复收藏 0 原文

~没有更多了~