Displaying Data
 

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

Re: Displaying Data
 

you need to add a group by

GROUP BY cust_no,......

Re: Displaying Data
 

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

Re: Displaying Data
 

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

Re: Displaying Data
 

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.

Re: Displaying Data
 

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