MS SQL

Displaying Data

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Sep 2008

Posts: 330

Reputation: freshfitz is an unknown quantity at this point

Solved Threads: 27

freshfitz

Offline

Posting Whiz

Displaying Data

Nov 30th, 2008

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

 Help with Code Tags 
 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.

http://Vortexamerica.com

•

Join Date: Aug 2008

Posts: 1,158

Reputation: dickersonka will become famous soon enough

Solved Threads: 136

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Displaying Data

Dec 1st, 2008

you need to add a group by

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

GROUP BY cust_no,......

Custom Application & Software Development
www.houseshark.net

•

Join Date: Sep 2008

Posts: 330

Reputation: freshfitz is an unknown quantity at this point

Solved Threads: 27

freshfitz

Offline

Posting Whiz

Re: Displaying Data

Dec 1st, 2008

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

http://Vortexamerica.com

•

Join Date: Aug 2008

Posts: 1,158

Reputation: dickersonka will become famous soon enough

Solved Threads: 136

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Displaying Data

Dec 1st, 2008

what about a distinct

Help with Code Tags

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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Sep 2008

Posts: 330

Reputation: freshfitz is an unknown quantity at this point

Solved Threads: 27

freshfitz

Offline

Posting Whiz

Re: Displaying Data

Dec 1st, 2008

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.

http://Vortexamerica.com