SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Logical errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sundayose
Starting Member

Uganda
21 Posts

Posted - 03/28/2013 :  01:52:15  Show Profile  Reply with Quote
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

USA
5071 Posts

Posted - 03/28/2013 :  02:59:49  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

sundayose
Starting Member

Uganda
21 Posts

Posted - 03/28/2013 :  11:38:46  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 03/28/2013 :  11:56:04  Show Profile  Visit russell's Homepage  Reply with Quote
http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
Go to Top of Page

sundayose
Starting Member

Uganda
21 Posts

Posted - 03/28/2013 :  12:27:51  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 03/28/2013 :  12:36:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000