Query question

Hi Experts,

This query below shows only results if there is a record stored in the table "bed2_opgaven" if there is a corresponding record stored in the table "bed2_aftelling". But it should also show records from the table "bed2_opgaven" if there where no corresponding records in the table "bed2_aftelling". But in that case the values of TotSpoedAfd and TotSpoedCSO should be presented as value 0.

I've added the query that should be changed and the one that creates the tables.

Thanks,

' ------------------------Query 1----------------------------
 
SELECT   DISTINCT
         BO.OpgaveID
        ,BO.Belegd
        ,BO.Gerserveerd
		,CAST(BO.SpoedAFD AS VARCHAR(10)) AS SpoedAFD
		,CAST(BO.SpoedCSO AS VARCHAR(10)) AS SpoedCSO
        ,BV.Vafdelingscode
        ,BV.specialismen
        ,BV.bedmax
        ,CONVERT(varchar(100),BA.TotSpoedAfd) + '/' + CONVERT(varchar(100),BO.SpoedAFD) AS TotSpoedAfd
        ,CONVERT(varchar(100),BA.TotSpoedCSO) + '/' + CONVERT(varchar(100),BO.SpoedCSO) AS TotSpoedCSO
FROM
      bed2_Opgaven BO
INNER JOIN 
      (SELECT DISTINCT VAfdelingsID, Vafdelingscode, specialismen, bedmax
       FROM bed_verpleegafdelingen
      )
      BV ON BV.VAfdelingsID = BO.VAfdelingsID
INNER JOIN 
      (SELECT OpgaveID, CAST(SUM(AfSpoedAfd) AS VARCHAR(10)) AS TotSpoedAfd, CAST(SUM(AfSpoedCSO) AS VARCHAR(10)) AS TotSpoedCSO
       FROM bed2_aftelling 
       GROUP BY OpgaveID
      )
      BA ON BA.OpgaveID = BO.OpgaveID "
 
' -----------------------------Table create query----------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bed_verpleegafdelingen]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[bed_verpleegafdelingen](
	[VAfdelingsID] [int] NOT NULL,
	[Vafdelingscode] [nvarchar](50) NULL,
	[specialismen] [nvarchar](50) NULL,
	[bedmax] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bed2_aftelling]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[bed2_aftelling](
	[AftellingsID] [int] NOT NULL,
	[OpgaveID] [int] NULL,
	[vafdelingID] [int] NULL,
	[AfSpoedAfd] [int] NULL,
	[AFSpoedCSO] [int] NULL,
	[opmerking] [nvarchar](max) NULL,
	[lastchange] [datetime] NULL,
	[door] [nvarchar](50) NULL,
	[gewensteafdID] [int] NULL,
	[aktief] [bit] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bed2_opgaven]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[bed2_opgaven](
	[OpgaveID] [int] NOT NULL,
	[VAfdelingsID] [int] NULL,
	[Belegd] [int] NULL,
	[Gerserveerd] [int] NULL,
	[SpoedAFD] [int] NULL,
	[SpoedCSO] [int] NULL,
	[aktief] [bit] NOT NULL
) ON [PRIMARY]
END
 
Open in New Window Select All

Select allOpen in new window