I'm trying to display data in cold fusion but when I add my history table I get duplicate listings. I've tried select distinct different joins. How do i join my history table and not get duplicate listings
My store procedure in MS SQL is
USE [Website]
GO
/****** Object: StoredProcedure [dbo].[CheckWebLogin] Script Date: 11/30/2008 09:54:13 ******/
/****** Created BY Ryan Fitzpatrick for Website Customer Interface Script Date: 11/30/2008 09:54:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CheckWebLogin]
@vcemail [varchar] (50) = '_',
@vcPassword [varchar] (50) = '_',
@vcSeason [smallint] = '',
@vcuid [varchar] (50) = '_',
@vccust_no [varchar] (50) = '_'
AS
SET NOCOUNT ON
SELECT
[dbo].[customer].[cust_no],
[dbo].[customer].[balance],
[dbo].[customer].[firstname],
[dbo].[customer].[lastname],
[dbo].[customer].[email],
[dbo].[customer].[password],
[dbo].[customer].[uid],
[dbo].[customer].[streetno],
[dbo].[customer].[streetnm],
[dbo].[customer].[suffix],
[dbo].[customer].[city],
[dbo].[customer].[state],
[dbo].[customer].[zip],
[dbo].[service].[price],
[dbo].[service].[serv_code],
[dbo].[service].[servstatus],
[dbo].[servcd].[servname],
[dbo].[servhist].[donedate],
[dbo].[employee].[name],
[dbo].[customer].[comp_id]
FROM
[dbo].[customer]
left outer JOIN [dbo].[program] ON [dbo].[customer].[cust_no] = [dbo].[program].[cust_no]
left outer JOIN [dbo].[service] ON [dbo].[program].[prog_id] = [dbo].[service].[prog_id]
left outer JOIN [dbo].[servcd] ON [dbo].[servcd].[serv_code] = [dbo].[service].[serv_code]
left outer JOIN [dbo].[servhist] ON [dbo].[service].[service_id] = [dbo].[servhist].[service_id]
left outer JOIN [dbo].[doneby] ON [dbo].[service].[service_id] = [dbo].[doneby].[service_id]
left outer JOIN [dbo].[employee] ON [dbo].[doneby].[emp_id] = [dbo].[employee].[emp_id]
WHERE
([dbo].[customer].[cust_no] = @vccust_no)
and [dbo].[program].[season] = @vcSeason
and [dbo].[service].[servstatus] <> 'n'
Order by dbo.service.serv_code
SET NOCOUNT OFF
My output
Round Name Status Done By Price Done Date
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L04 Grub Guard Round Completed Justin McGill 01-Aug-08 $417.60
L05 Maintenance Round Completed Justin McGill 01-Aug-08 $208.80
L07 Revitalizer Round Completed Ahmed Shaw 29-Aug-08 $208.80
L08 Anytime Lime Round Completed Brendon Massey 09-Jul-08 $208.80
L09 Winter Prep Round Completed Steven Randazzo 18-Nov-08 $208.80
T01 Tree & Shrub Early Spring Skip to late in year $226.80
T02 Tree & Shrub Late Spring Skip Round $226.80
T03 Tree & Shrub Early Summer Skip to late in year $226.80
T04 Tree & Shrub Late Summer Skip to late in year $226.80
T05 Tree & Shrub Fall Skip to late in year $226.80
T06 Tree & Shrub Winterizer Scheduled $226.80
X1 Bug Buster Program Round Completed Hugo Hernandez Millan 27-May-08 $119.94
X2 Bug Buster Program Round Completed Brendon Massey 09-Jul-08 $119.94
X3 Bug Buster Program Round Completed Justin McGill 01-Aug-08 $119.94
Your Current Balance is: $0.00
---------------------------------
Now when i add
left outer JOIN [dbo].[history] ON [dbo].[history].[cust_no] = [dbo].[customer].[cust_no]
I get the data listed 10 times:
Round Name Status Done By Price Done Date
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L01 Rejuvenation Round Completed Brian Boyes 11-Mar-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L02 Crabgrass Barrier Round Completed Hugo Hernandez Millan 27-May-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $208.80
L03 Weed Manager Round Completed Brendon Massey 09-Jul-08 $20