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

you need to add a group by

GROUP BY cust_no,......

I tried that and I get this

Column 'dbo.customer.balance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The only way I can get rid of the message is if i add all my selects to the group by, But I get the same output with multiple listings

what about a distinct

select distinct cust_no

its hard to see where the data is coming from here, if you are wanting the balance only from customer.balance

It's hard to diagnose without seeing the table structures. But basically I have 3 tables customer, service, and program customer has 1 line program has 1 line item and service has 1 line item. History can have 1 - 1000 line items for each customer number so when i inner join the history table it wants to list the customer line item info for each history line item in the history table. This might be a could fusion question maybe in cold fusion I can filter my loop through statement.

I'm going to close this thread, I think it's a problem in the cold fusion code. Thanks for you help

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.