Linking two table based on composite key to show all related record and 0 for unrelated records

pnasz · September 5, 2021, 6:58am

I have two tables

BPLAN-n and PERFORCEBPCOMP

USE [ProjEve]
GO
/****** Object: Table [dbo].[BPLAN-n] Script Date: 9/5/2021 9:27:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BPLAN-n](
[FY] varchar NOT NULL,
[REG] varchar NOT NULL,
[Factory] varchar NOT NULL,
[TU] [decimal](18, 2) NOT NULL,
[TSP] [decimal](18, 2) NULL,
[TES] [decimal](18, 2) NULL,
[TEGM] [decimal](18, 2) NULL,
[TMS] [decimal](18, 2) NULL,
[TMGM] [decimal](18, 2) NULL,
[TTGMA] [decimal](18, 2) NULL,
[STATUS] varchar NOT NULL,
CONSTRAINT [PK_BPLAN-n] PRIMARY KEY CLUSTERED
(
[FY] ASC,
[REG] ASC,
[Factory] ASC,
[STATUS] 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 ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PERFORCEBPCOMP] Script Date: 9/5/2021 9:27:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PERFORCEBPCOMP](
[FY] varchar NOT NULL,
[Factory] varchar NOT NULL,
[PCOUNT] [int] NOT NULL,
[UNIT] [decimal](18, 2) NULL,
[CITOTPU] [decimal](18, 2) NULL,
[SP] [decimal](18, 2) NULL,
[TOT1] [decimal](18, 2) NULL,
[EQS] [decimal](18, 2) NULL,
[INS] [decimal](18, 2) NULL,
[MS] [decimal](18, 2) NULL,
[GMSAR] [decimal](18, 2) NULL,
[GM] [decimal](18, 2) NULL,
[REG] varchar NOT NULL,
[STATUS] varchar NULL,
CONSTRAINT [PK_PERFORCEBPCOMP] PRIMARY KEY CLUSTERED
(
[FY] ASC,
[Factory] ASC,
[REG] 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 ANSI_PADDING OFF
GO
INSERT [dbo].[BPLAN-n] ([FY], [REG], [Factory], [TU], [TSP], [TES], [TEGM], [TMS], [TMGM], [TTGMA], [STATUS]) VALUES (N'FY2021', N'Central Region', N'AMEC', CAST(102.00 AS Decimal(18, 2)), CAST(19140000.00 AS Decimal(18, 2)), CAST(12060000.00 AS Decimal(18, 2)), CAST(647400.00 AS Decimal(18, 2)), CAST(600000.00 AS Decimal(18, 2)), CAST(29400.00 AS Decimal(18, 2)), CAST(1020000.00 AS Decimal(18, 2)), N'P0')
INSERT [dbo].[BPLAN-n] ([FY], [REG], [Factory], [TU], [TSP], [TES], [TEGM], [TMS], [TMGM], [TTGMA], [STATUS]) VALUES (N'FY2021', N'Central Region', N'MELINA', CAST(102.00 AS Decimal(18, 2)), CAST(19140000.00 AS Decimal(18, 2)), CAST(12060000.00 AS Decimal(18, 2)), CAST(647400.00 AS Decimal(18, 2)), CAST(600000.00 AS Decimal(18, 2)), CAST(29400.00 AS Decimal(18, 2)), CAST(1020000.00 AS Decimal(18, 2)), N'P0')
INSERT [dbo].[BPLAN-n] ([FY], [REG], [Factory], [TU], [TSP], [TES], [TEGM], [TMS], [TMGM], [TTGMA], [STATUS]) VALUES (N'FY2021', N'Central Region', N'MHE', CAST(102.00 AS Decimal(18, 2)), CAST(19140000.00 AS Decimal(18, 2)), CAST(12060000.00 AS Decimal(18, 2)), CAST(647400.00 AS Decimal(18, 2)), CAST(600000.00 AS Decimal(18, 2)), CAST(29400.00 AS Decimal(18, 2)), CAST(1020000.00 AS Decimal(18, 2)), N'P0')
INSERT [dbo].[BPLAN-n] ([FY], [REG], [Factory], [TU], [TSP], [TES], [TEGM], [TMS], [TMGM], [TTGMA], [STATUS]) VALUES (N'FY2021', N'Central Region', N'RESCO', CAST(102.00 AS Decimal(18, 2)), CAST(19140000.00 AS Decimal(18, 2)), CAST(12060000.00 AS Decimal(18, 2)), CAST(647400.00 AS Decimal(18, 2)), CAST(600000.00 AS Decimal(18, 2)), CAST(29400.00 AS Decimal(18, 2)), CAST(1020000.00 AS Decimal(18, 2)), N'P0')
INSERT [dbo].[BPLAN-n] ([FY], [REG], [Factory], [TU], [TSP], [TES], [TEGM], [TMS], [TMGM], [TTGMA], [STATUS]) VALUES (N'FY2021', N'Central Region', N'SMEC', CAST(102.00 AS Decimal(18, 2)), CAST(19140000.00 AS Decimal(18, 2)), CAST(12060000.00 AS Decimal(18, 2)), CAST(647400.00 AS Decimal(18, 2)), CAST(600000.00 AS Decimal(18, 2)), CAST(29400.00 AS Decimal(18, 2)), CAST(1020000.00 AS Decimal(18, 2)), N'P0')
INSERT [dbo].[PERFORCEBPCOMP] ([FY], [Factory], [PCOUNT], [UNIT], [CITOTPU], [SP], [TOT1], [EQS], [INS], [MS], [GMSAR], [GM], [REG], [STATUS]) VALUES (N'FY2021', N'AMEC', 12, CAST(45.00 AS Decimal(18, 2)), CAST(697855.00 AS Decimal(18, 2)), CAST(11840222.61 AS Decimal(18, 2)), CAST(10044705.59 AS Decimal(18, 2)), CAST(6475269.88 AS Decimal(18, 2)), CAST(5091552.73 AS Decimal(18, 2)), CAST(273400.00 AS Decimal(18, 2)), CAST(1795517.02 AS Decimal(18, 2)), CAST(15.16 AS Decimal(18, 2)), N'Central Region', N'P0')
INSERT [dbo].[PERFORCEBPCOMP] ([FY], [Factory], [PCOUNT], [UNIT], [CITOTPU], [SP], [TOT1], [EQS], [INS], [MS], [GMSAR], [GM], [REG], [STATUS]) VALUES (N'FY2021', N'MHE', 11, CAST(15.00 AS Decimal(18, 2)), CAST(188330.31 AS Decimal(18, 2)), CAST(1439799.89 AS Decimal(18, 2)), CAST(1262708.75 AS Decimal(18, 2)), CAST(1028470.27 AS Decimal(18, 2)), CAST(347609.63 AS Decimal(18, 2)), CAST(63720.00 AS Decimal(18, 2)), CAST(177091.14 AS Decimal(18, 2)), CAST(12.30 AS Decimal(18, 2)), N'Central Region', N'P0')
INSERT [dbo].[PERFORCEBPCOMP] ([FY], [Factory], [PCOUNT], [UNIT], [CITOTPU], [SP], [TOT1], [EQS], [INS], [MS], [GMSAR], [GM], [REG], [STATUS]) VALUES (N'FY2021', N'RYODEN', 2, CAST(2.00 AS Decimal(18, 2)), CAST(26974.68 AS Decimal(18, 2)), CAST(194739.46 AS Decimal(18, 2)), CAST(176202.27 AS Decimal(18, 2)), CAST(134132.33 AS Decimal(18, 2)), CAST(54607.13 AS Decimal(18, 2)), CAST(6000.00 AS Decimal(18, 2)), CAST(18537.19 AS Decimal(18, 2)), CAST(9.52 AS Decimal(18, 2)), N'Central Region', N'P0')
INSERT [dbo].[PERFORCEBPCOMP] ([FY], [Factory], [PCOUNT], [UNIT], [CITOTPU], [SP], [TOT1], [EQS], [INS], [MS], [GMSAR], [GM], [REG], [STATUS]) VALUES (N'FY2021', N'TMEC', 1, CAST(1.00 AS Decimal(18, 2)), CAST(63630.00 AS Decimal(18, 2)), CAST(395040.04 AS Decimal(18, 2)), CAST(364157.38 AS Decimal(18, 2)), CAST(283106.81 AS Decimal(18, 2)), CAST(106433.23 AS Decimal(18, 2)), CAST(5500.00 AS Decimal(18, 2)), CAST(30882.66 AS Decimal(18, 2)), CAST(7.82 AS Decimal(18, 2)), N'Central Region', N'P0')

I have created view to get the record

SELECT dbo.PERFORCEBPCOMP.FY, dbo.PERFORCEBPCOMP.Factory, dbo.PERFORCEBPCOMP.REG, dbo.PERFORCEBPCOMP.PCOUNT, dbo.[BPLAN-n].TU,
dbo.PERFORCEBPCOMP.UNIT, dbo.[BPLAN-n].TSP, dbo.PERFORCEBPCOMP.SP, dbo.[BPLAN-n].TES, dbo.PERFORCEBPCOMP.EQS, dbo.[BPLAN-n].TMS,
dbo.PERFORCEBPCOMP.MS, dbo.[BPLAN-n].TTGMA, dbo.PERFORCEBPCOMP.GMSAR, dbo.PERFORCEBPCOMP.GM, dbo.PERFORCEBPCOMP.TOT1,
dbo.PERFORCEBPCOMP.STATUS, dbo.[BPLAN-n].TEGM, dbo.[BPLAN-n].TMGM
FROM dbo.[BPLAN-n] INNER JOIN
dbo.PERFORCEBPCOMP ON dbo.[BPLAN-n].FY = dbo.PERFORCEBPCOMP.FY AND dbo.[BPLAN-n].STATUS = dbo.PERFORCEBPCOMP.STATUS AND
dbo.[BPLAN-n].REG = dbo.PERFORCEBPCOMP.REG AND dbo.[BPLAN-n].Factory = dbo.PERFORCEBPCOMP.Factory

[BPLAN-n] table has all the factory and has target for each factory
PERFORCEBPCOMP has the records for particular month but some time it will not return record for particular factory reason there is no sales that month, so i want to show the record with target for all factory and zero value if no sales.

can you help me.

harishgg1 · September 5, 2021, 1:31pm

please google search LEFT join or RIGHT join ..
lots of very very nice articles , videos

JeffModen · September 5, 2021, 3:53pm

@pnasz

Man, you really did try to go the extra mile with the code you included in your post. So why did @harishgg1 give the the "see Google" answer? It's very likely because the code you provided doesn't actually work. It gives you string truncation errors instead. Now, in this case, it's a pretty easy fix but it means that you didn't try your own code before you posted it.

I took the time to fix the code so that your example works. I won't have the time or interest to always do such a thing and neither will a lot of others. Just sayin... always check your code before posting it by running it somewhere your self.

Also, your description refers to a "month" period. There's no monthly data in either of the tables you posted.

So I tried a LEFT JOIN on the "view" you posted. It returns NULLs for the things that you wanted returned as zeros so you could just change those in the SELECT column list to be [displaycolumnname] = ISNULL([actualfullcolumnname,0])

yosiasz · September 5, 2021, 4:37pm

also please save our eyes as well as using aliases

SELECT 
 p.FY, 
 p.Factory, 
 p.REG, 
 p.PCOUNT, 
  b.TU, 
 p.UNIT, 
  b.TSP, 
 p.SP, 
  b.TES, 
 p.EQS, 
  b.TMS, 
 p.MS, 
  b.TTGMA, 
 p.GMSAR, 
 p.GM, 
 p.TOT1, 
 p.STATUS, 
  b.TEGM, 
  b.TMGM 
FROM dbo.[BPLAN - n] b
  INNER JOIN dbo.PERFORCEBPCOMP p ON b.FY = p.FY 
  AND b.STATUS = p.STATUS 
  AND b.REG = p.REG 
  AND b.Factory = p.Factory

pnasz · September 7, 2021, 5:03am

thanks a lot for info.

pnasz · September 7, 2021, 5:04am

thanks.