Need help on T-SQL

• Adelia

  SSC-Addicted

  Points: 422

  More actions

  October 21, 2021 at 11:50 pm

  Go to Answer

  #3943056

  I've table and data as script below,

  CREATE TABLE [dbo].[ClientProjects](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [ClientId] [int] NOT NULL,
  [ProjectId] [int] NOT NULL,
   CONSTRAINT [PK_ClientProjects] PRIMARY KEY CLUSTERED 
  (
  [Id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  /****** Object:  Table [dbo].[Project]    Script Date: 22/10/2021 7:41:27 AM ******/SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[Project](
  [ProjectId] [int] IDENTITY(1,1) NOT NULL,
  [ProjectName] [nvarchar](450) NULL,
   CONSTRAINT [PK_Project_1] PRIMARY KEY CLUSTERED 
  (
  [ProjectId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET IDENTITY_INSERT [dbo].[ClientProjects] ON 
  GO
  INSERT [dbo].[ClientProjects] ([Id], [ClientId], [ProjectId]) VALUES (1, 4, 1)
  GO
  SET IDENTITY_INSERT [dbo].[ClientProjects] OFF
  GO
  SET IDENTITY_INSERT [dbo].[Project] ON 
  GO
  INSERT [dbo].[Project] ([ProjectId], [ProjectName]) VALUES (1, N'maybank4u')
  GO
  INSERT [dbo].[Project] ([ProjectId], [ProjectName]) VALUES (2, N'Salahudin')
  GO
  INSERT [dbo].[Project] ([ProjectId], [ProjectName]) VALUES (3, N'test')
  GO
  INSERT [dbo].[Project] ([ProjectId], [ProjectName]) VALUES (4, N'maybank')
  GO
  SET IDENTITY_INSERT [dbo].[Project] OFF
  GO
  

  Table: Project

  

   

  Table: ClientProjects

  

   

  How T-SQL looks like to getting result as following,

  ProjectId      | ProjectName       | Selected
  ---------------------------------------------
  1                maybank4u           true
  2                Salahudin           false
  3                test                false
  4                maybank             false

   

  All Project must be returned

  • This topic was modified 3 years, 2 months ago by  Adelia.
  • This topic was modified 3 years, 2 months ago by  Adelia.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 22, 2021 at 1:34 am

  Answer

  #3943078

  Read up on OUTER joins.  This will do it for you.

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

   SELECT  p.ProjectId
          ,p.ProjectName
          ,Selected = IIF(cp.ProjectID>0,'true','false')
     FROM      dbo.Project         p
     LEFT JOIN dbo.ClientProjects cp
       ON p.ProjectId = cp.ProjectId
    ORDER BY p.ProjectId
  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Adelia

  SSC-Addicted

  Points: 422

  More actions

  October 22, 2021 at 6:57 am

  #3943131

  Thanks