Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1030

Nov 30th, 2008

Displaying Data

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
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
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

Last edited by freshfitz; Nov 30th, 2008 at 12:04 pm.

Similar Threads

Displaying data on same page without reloading page. in JavaScript / DHTML / AJAX
How to insert data into database? in JSP
Winsock C++ help: receiving data in C++
displaying data onto a web page in ASP.NET
Problem with displaying data.. in PHP

freshfitz

Reputation Points: 12

Solved Threads: 36

Posting Pro in Training

Offline

436 posts
since Sep 2008

Permalink

Dec 1st, 2008

Re: Displaying Data

you need to add a group by

MS SQL Syntax (Toggle Plain Text)

GROUP BY cust_no,......

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Dec 1st, 2008

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

freshfitz

Reputation Points: 12

Solved Threads: 36

Posting Pro in Training

Offline

436 posts
since Sep 2008

Permalink

Dec 1st, 2008

Re: Displaying Data

what about a distinct

MS SQL Syntax (Toggle Plain Text)

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Dec 1st, 2008

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.

freshfitz

Reputation Points: 12

Solved Threads: 36

Posting Pro in Training

Offline

436 posts
since Sep 2008

Permalink

Dec 1st, 2008

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

freshfitz

Reputation Points: 12

Solved Threads: 36

Posting Pro in Training

Offline

436 posts
since Sep 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Please help

Next Thread in MS SQL Forum Timeline: a simple application

DaniWeb Message

Cancel Changes

User Name
Password