Logical errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Logical errors

Author

Topic

sundayose
Starting Member

21 Posts

Posted - 2013-03-28 : 01:52:15

Hi all,

please help to identify the reason why am getting logical errors in my output for the query below.

use MSDB;

select DISTINCT
[dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE AS IssuanceDate,
[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE,
[dbo].[tbl_SALES_TEAM_MASTER].[str_SALES_TEAM_NAME],
[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE As VouchersSold,
[dbo].[tbl_VOUCHER_CAPTURE].dt_VOUCHER_ISSUE_DATE AS SalesDate,
[dbo].[tbl_SALES_EXECUTIVE_MASTER].str_SALES_EXECUTIVE_NAME AS BCCName,
[dbo].[tbl_DISTRIBUTOR_MASTER].[str_DISTRIBUTOR_NAME] AS CBDName,
[dbo].[tbl_DISTRICT_MASTER].str_DISTRICT_NAME CBD_District

FROM
[dbo].[tbl_VOUCHER_ISSUE]

Left Join [dbo].[tbl_VOUCHER_ISSUE_DETAIL]
ON [dbo].[tbl_VOUCHER_ISSUE].int_VOUCHER_ISSUE_ID = [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_ID
Left Join [dbo].[tbl_SALES_TEAM_MASTER]
ON [dbo].[tbl_VOUCHER_ISSUE].int_SALES_TEAM_ID = [dbo].[tbl_SALES_TEAM_MASTER].int_SALES_TEAM_ID
Left Join [dbo].[tbl_VOUCHER_CAPTURE_DETAIL]
ON [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID = [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID
Left Join [dbo].[tbl_VOUCHER_CAPTURE]
ON [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_CAPTURE_ID = [dbo].[tbl_VOUCHER_CAPTURE].int_VOUCHER_CAPTURE_ID
Left Join [dbo].[tbl_SALES_EXECUTIVE_MASTER]
ON [dbo].[tbl_VOUCHER_CAPTURE].int_SALES_EXECUTIVE_ID = [dbo].[tbl_SALES_EXECUTIVE_MASTER].int_SALES_EXECUTIVE_ID
Left Join [dbo].[tbl_DISTRIBUTOR_MASTER]
ON [dbo].[tbl_VOUCHER_CAPTURE].[int_DISTRIBUTOR_ID] = [dbo].[tbl_DISTRIBUTOR_MASTER].[int_DISTRIBUTOR_ID]
Left Join [dbo].[tbl_DISTRICT_MASTER]
ON [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRICT_ID = [dbo].[tbl_DISTRICT_MASTER].int_DISTRICT_ID

WHERE
[dbo].[tbl_VOUCHER_ISSUE_DETAIL].str_BARCODE LIKE 'FPUG%' AND
[dbo].[tbl_VOUCHER_ISSUE].dt_VOUCHER_ISSUE_DATE between '2012-06-01' and '2012-06-30' AND
[dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_STATUS !=5

Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-28 : 02:59:49

1. Why are you storing user objects in msdb?
2. Please post DDL, sample data and expected output. No one can guess what your logical errors are without knowing what you're trying to accomplish.

sundayose
Starting Member

21 Posts

Posted - 2013-03-28 : 11:38:46

I think what I need is a tutorial which will teach me on how to join multiple table.

Most of the tutorials I am getting on the internet do not show how to deal with multiple tables like 5 or more thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-28 : 11:56:04

http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx

sundayose
Starting Member

21 Posts

Posted - 2013-03-28 : 12:27:51

Below is the DLL for my tables.

USE [MSIU]
GO
/****** Object: Table [dbo].[tbl_DISTRIBUTOR_MASTER] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_DISTRIBUTOR_MASTER](
[int_DISTRIBUTOR_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[str_DISTRIBUTOR_CODE] [varchar](50) NULL,
[str_DISTRIBUTOR_NAME] [varchar](100) NULL,
[int_ORAGANISATION_TYPE_ID] [int] NULL,
[str_PROPRIETOR_NAME] [varchar](50) NULL,
[str_OCCUPATION] [varchar](50) NULL,
[str_DESIGNATION] [varchar](50) NULL,
[str_CONTACT_NUMBER] [varchar](50) NULL,
[str_EMAIL] [varchar](100) NULL,
[str_ACCEPTABLE_CREDIT_LIMIT] [varchar](50) NULL,
[dec_ACCEPTED_DISCOUNT] [decimal](18, 2) NULL,
[str_POSTAL_ADDRESS] [varchar](200) NULL,
[int_VILLAGE_ID] [bigint] NOT NULL,
[int_PARISH_ID] [bigint] NOT NULL,
[int_SUB_COUNTY_ID] [bigint] NOT NULL,
[int_COUNTY_ID] [bigint] NOT NULL,
[int_SUB_DISTRICT_ID] [bigint] NOT NULL,
[int_DISTRICT_ID] [bigint] NOT NULL,
[int_COUNTRY_ID] [bigint] NOT NULL,
[bln_ACTIVE] [bit] NULL,
[bln_ISDELETED] [bit] NOT NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
CONSTRAINT [PK_tbl_DISTRIBUTOR_MASTER] PRIMARY KEY CLUSTERED
(
[int_DISTRIBUTOR_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_SALES_EXECUTIVE_MASTER] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_SALES_EXECUTIVE_MASTER](
[int_SALES_EXECUTIVE_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[str_SALES_EXECUTIVE_CODE] [varchar](50) NULL,
[str_SALES_EXECUTIVE_NAME] [varchar](100) NULL,
[str_EMAIL] [varchar](100) NULL,
[str_GENDER] [varchar](50) NULL,
[str_CONTACT_NUMBER] [varchar](50) NULL,
[int_ACCEPTABLE_CREDIT_LIMIT] [int] NULL,
[str_AREA_OF_OPERATION] [varchar](50) NULL,
[int_SALES_TEAM_ID] [bigint] NULL,
[str_PHYSICAL_ADDRESS] [varchar](200) NULL,
[bln_ISACTIVE] [bit] NULL,
[bln_ISDELETED] [bit] NOT NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
[dt_BIRTH_DATE] [datetime] NULL,
CONSTRAINT [PK_tbl_SALSE_EXECUTIVE_MASTER] PRIMARY KEY CLUSTERED
(
[int_SALES_EXECUTIVE_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_SALES_TEAM_MASTER] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_SALES_TEAM_MASTER](
[int_SALES_TEAM_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[str_SALES_TEAM_CODE] [varchar](50) NULL,
[str_SALES_TEAM_NAME] [varchar](100) NULL,
[str_DRIVER_NAME] [varchar](100) NULL,
[str_DESCRIPTION] [varchar](1000) NULL,
[bln_ISACTIVE] [bit] NOT NULL,
[bln_ISDELETED] [bit] NOT NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
CONSTRAINT [PK_tbl_SALES_TEAM_MASTER] PRIMARY KEY CLUSTERED
(
[int_SALES_TEAM_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_VOUCHER_CAPTURE] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_VOUCHER_CAPTURE](
[int_VOUCHER_CAPTURE_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[int_SALES_TEAM_ID] [bigint] NULL,
[int_SALES_EXECUTIVE_ID] [bigint] NULL,
[int_DISTRIBUTOR_ID] [bigint] NULL,
[dt_VOUCHER_ISSUE_DATE] [datetime] NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
[int_SALES_AMOUNT] [decimal](18, 2) NULL,
CONSTRAINT [PK_tbl_VOUCHER_CAPTURE] PRIMARY KEY CLUSTERED
(
[int_VOUCHER_CAPTURE_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_VOUCHER_CAPTURE_DETAIL] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_VOUCHER_CAPTURE_DETAIL](
[int_VOUCHER_CAPTURE_DETAIL_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[int_VOUCHER_CAPTURE_ID] [bigint] NOT NULL,
[int_SEQ_NO] [bigint] NULL,
[int_BARCODE_DETAIL_ID] [bigint] NOT NULL,
[int_BARCODE_ID] [bigint] NOT NULL,
[int_VOUCHER_ISSUE_ID] [bigint] NOT NULL,
[int_VOUCHER_ISSUE_DETAIL_ID] [bigint] NOT NULL,
[dt_APPLICABLE_FROM] [datetime] NULL,
[int_VOUCHER_NUMBER] [int] NULL,
[str_BARCODE] [varchar](15) NULL,
[int_STATUS] [int] NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
[bln_ISDELETE] [bit] NULL,
[int_PROJECT_TYPE] [int] NULL,
CONSTRAINT [PK_tbl_VOUCHER_CAPTURE_DETAIL] PRIMARY KEY CLUSTERED
(
[int_VOUCHER_CAPTURE_DETAIL_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_VOUCHER_ISSUE] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_VOUCHER_ISSUE](
[int_VOUCHER_ISSUE_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[int_SALES_TEAM_ID] [bigint] NULL,
[dt_VOUCHER_ISSUE_DATE] [datetime] NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
CONSTRAINT [PK_tbl_VOUCHER_ISSUE] PRIMARY KEY CLUSTERED
(
[int_VOUCHER_ISSUE_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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_VOUCHER_ISSUE_DETAIL] Script Date: 3/28/2013 7:23:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_VOUCHER_ISSUE_DETAIL](
[int_VOUCHER_ISSUE_DETAIL_ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[int_VOUCHER_ISSUE_ID] [bigint] NOT NULL,
[int_SEQ_NO] [bigint] NULL,
[int_BARCODE_DETAIL_ID] [bigint] NOT NULL,
[int_BARCODE_ID] [bigint] NOT NULL,
[dt_APPLICABLE_FROM] [datetime] NULL,
[int_VOUCHER_NUMBER] [int] NULL,
[str_BARCODE] [varchar](15) NULL,
[int_STATUS] [int] NULL,
[int_DISTRIBUTOR_ID] [bigint] NOT NULL,
[str_CREATED_BY] [varchar](50) NULL,
[dt_CREATED_DATE] [datetime] NULL,
[str_UPDATED_BY] [varchar](50) NULL,
[dt_UPDATED_DATE] [datetime] NULL,
[bln_ISDELETE] [bit] NULL,
[int_PROJECT_TYPE] [int] NULL,
CONSTRAINT [PK_tbl_VOUCHER_ISSUE_DETAIL] PRIMARY KEY CLUSTERED
(
[int_VOUCHER_ISSUE_DETAIL_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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_VILLAGE_ID] DEFAULT ((0)) FOR [int_VILLAGE_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_PARISH_ID] DEFAULT ((0)) FOR [int_PARISH_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_SUB_COUNTY_ID] DEFAULT ((0)) FOR [int_SUB_COUNTY_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_COUNTY_ID] DEFAULT ((0)) FOR [int_COUNTY_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_SUB_DISTRICT_ID] DEFAULT ((0)) FOR [int_SUB_DISTRICT_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_DISTRICT_ID] DEFAULT ((0)) FOR [int_DISTRICT_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_int_COUNTRY_ID] DEFAULT ((0)) FOR [int_COUNTRY_ID]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_bln_ACTIVE] DEFAULT ((1)) FOR [bln_ACTIVE]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] ADD CONSTRAINT [DF_tbl_DISTRIBUTOR_MASTER_bln_ISDELETED] DEFAULT ((0)) FOR [bln_ISDELETED]
GO
ALTER TABLE [dbo].[tbl_SALES_EXECUTIVE_MASTER] ADD CONSTRAINT [DF_tbl_SALSE_EXECUTIVE_MASTER_bln_ISACTIVE] DEFAULT ((1)) FOR [bln_ISACTIVE]
GO
ALTER TABLE [dbo].[tbl_SALES_EXECUTIVE_MASTER] ADD CONSTRAINT [DF_tbl_SALSE_EXECUTIVE_MASTER_bln_ISDELETED] DEFAULT ((0)) FOR [bln_ISDELETED]
GO
ALTER TABLE [dbo].[tbl_SALES_TEAM_MASTER] ADD CONSTRAINT [DF_tbl_SALES_TEAM_MASTER_bln_ISDELETED1] DEFAULT ((1)) FOR [bln_ISACTIVE]
GO
ALTER TABLE [dbo].[tbl_SALES_TEAM_MASTER] ADD CONSTRAINT [DF_tbl_SALES_TEAM_MASTER_bln_ISDELETED] DEFAULT ((0)) FOR [bln_ISDELETED]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE_DETAIL] ADD CONSTRAINT [DF_tbl_VOUCHER_CAPTURE_DETAIL_bln_ISDELETE] DEFAULT ((0)) FOR [bln_ISDELETE]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE_DETAIL] ADD CONSTRAINT [DF_Table_1_int_CLIENT_ID] DEFAULT ((0)) FOR [int_DISTRIBUTOR_ID]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE_DETAIL] ADD CONSTRAINT [DF_tbl_VOUCHER_ISSUE_DETAIL_bln_ISDELETE] DEFAULT ((0)) FOR [bln_ISDELETE]
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] WITH CHECK ADD CONSTRAINT [FK_tbl_DISTRIBUTOR_MASTER_tbl_VILLAGE_MASTER] FOREIGN KEY([int_VILLAGE_ID])
REFERENCES [dbo].[tbl_VILLAGE_MASTER] ([int_VILLAGE_ID])
GO
ALTER TABLE [dbo].[tbl_DISTRIBUTOR_MASTER] CHECK CONSTRAINT [FK_tbl_DISTRIBUTOR_MASTER_tbl_VILLAGE_MASTER]
GO
ALTER TABLE [dbo].[tbl_SALES_EXECUTIVE_MASTER] WITH CHECK ADD CONSTRAINT [FK_tbl_SALES_EXECUTIVE_MASTER_tbl_SALES_TEAM_MASTER] FOREIGN KEY([int_SALES_TEAM_ID])
REFERENCES [dbo].[tbl_SALES_TEAM_MASTER] ([int_SALES_TEAM_ID])
GO
ALTER TABLE [dbo].[tbl_SALES_EXECUTIVE_MASTER] CHECK CONSTRAINT [FK_tbl_SALES_EXECUTIVE_MASTER_tbl_SALES_TEAM_MASTER]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] WITH CHECK ADD CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_DISTRIBUTOR_MASTER] FOREIGN KEY([int_DISTRIBUTOR_ID])
REFERENCES [dbo].[tbl_DISTRIBUTOR_MASTER] ([int_DISTRIBUTOR_ID])
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] CHECK CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_DISTRIBUTOR_MASTER]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] WITH CHECK ADD CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_SALES_EXECUTIVE_MASTER] FOREIGN KEY([int_SALES_EXECUTIVE_ID])
REFERENCES [dbo].[tbl_SALES_EXECUTIVE_MASTER] ([int_SALES_EXECUTIVE_ID])
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] CHECK CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_SALES_EXECUTIVE_MASTER]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] WITH CHECK ADD CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_SALES_TEAM_MASTER] FOREIGN KEY([int_SALES_TEAM_ID])
REFERENCES [dbo].[tbl_SALES_TEAM_MASTER] ([int_SALES_TEAM_ID])
GO
ALTER TABLE [dbo].[tbl_VOUCHER_CAPTURE] CHECK CONSTRAINT [FK_tbl_VOUCHER_CAPTURE_tbl_SALES_TEAM_MASTER]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE] WITH CHECK ADD CONSTRAINT [FK_tbl_VOUCHER_ISSUE_tbl_SALES_TEAM_MASTER] FOREIGN KEY([int_SALES_TEAM_ID])
REFERENCES [dbo].[tbl_SALES_TEAM_MASTER] ([int_SALES_TEAM_ID])
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE] CHECK CONSTRAINT [FK_tbl_VOUCHER_ISSUE_tbl_SALES_TEAM_MASTER]
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE_DETAIL] WITH CHECK ADD CONSTRAINT [FK_tbl_VOUCHER_ISSUE_DETAIL_tbl_BARCODE_DETAIL] FOREIGN KEY([int_BARCODE_DETAIL_ID])
REFERENCES [dbo].[tbl_BARCODE_DETAIL] ([int_BARCODE_DETAIL_ID])
GO
ALTER TABLE [dbo].[tbl_VOUCHER_ISSUE_DETAIL] CHECK CONSTRAINT [FK_tbl_VOUCHER_ISSUE_DETAIL_tbl_BARCODE_DETAIL]
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-28 : 12:36:58

Posting DDL is a start. Now we need the data (DML) and expected output. Below are some links to help you provide that in a consumable format.

My wild-ass guess is that you are applying the predicates in the WHERE clause and that they needs to be moved up to the JOIN clause. You have, affectivly, made the outer joins into inner joins by putting the predicate in the where clause.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources