Query with Header & Detail

[Chris White]

Here's my tables:
[ServiceCategory]
[ServiceCatID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
[ServiceCategory] [char] (50) NULL ,

[Services]
[ServiceID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
[ServiceCatID] [int] NULL ,
[ServiceName] [char] (100) NULL ,
[ServicePrice] [money] NULL ,

[ServiceDetails]
[ServiceDetailID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
[Detail] [char] (50) NULL ,

[ServiceDetailJoin]
[ServiceID] [int] NOT NULL (PRIMARY KEY)
[ServiceDetailID] [int] NOT NULL (PRIMARY KEY)

I want to show all of this in a single table. I have ServiceCategory and
ServiceName, ServicePrice coming across in a single query and displayed in a
single table. I have a variable keeping track of Service Name and only
printing it(within it's own table row) when it changes.
Here's a copy of the output, although it's kind of ugly.

Oil Service (ServiceCategory)
Oil Change Service $26.95
Premier Oil Change Service with BG MOA Package (Price does not include
synthetic oil) $32.95
Transmission Service (ServiceCategory)
Complete System Power Flush with BG Flush ATC Package
$149.95
Upper Induction Service (ServiceCategory)
All with BG Cleaning Packages without EGR $189.00
Throttle Plate Cleaning $78.90
Induction Service, 3 Part $109.95

Here's the problem, some of these Services have ServiceDetails.Details.
Besides the fact that I'm still having problems with my query as it's part
of a Many to Many junction table, there's is going to have to be several
mores variables to track and only print when changed.

My other option is to change the Query and rehit the database for each
detail line(Maybe 20 seperate Recordset and database hits per page)

Which is the best method?

[Ken Schaefer]

The best way is to hit the database once, and return all the info you need.

Then, in your presentation layer code (ASP, PHP, whatever), you use some
conditional logic to work out when to display headers and details

cheers
Ken

"Chris White" <nothanks.com> wrote in message
news:eU2dr74cEHA.1424@tk2msftngp13.phx.gbl...

> Here's my tables:
> [ServiceCategory]
> [ServiceCatID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [ServiceCategory] [char] (50) NULL ,
>
> [Services]
> [ServiceID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [ServiceCatID] [int] NULL ,
> [ServiceName] [char] (100) NULL ,
> [ServicePrice] [money] NULL ,
>
> [ServiceDetails]
> [ServiceDetailID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [Detail] [char] (50) NULL ,
>
> [ServiceDetailJoin]
> [ServiceID] [int] NOT NULL (PRIMARY KEY)
> [ServiceDetailID] [int] NOT NULL (PRIMARY KEY)
>
> I want to show all of this in a single table. I have ServiceCategory and
> ServiceName, ServicePrice coming across in a single query and displayed in

a

> single table. I have a variable keeping track of Service Name and only
> printing it(within it's own table row) when it changes.
> Here's a copy of the output, although it's kind of ugly.
>
> Oil Service (ServiceCategory)
> Oil Change Service $26.95
> Premier Oil Change Service with BG MOA Package (Price does not include
> synthetic oil) $32.95
> Transmission Service (ServiceCategory)
> Complete System Power Flush with BG Flush ATC Package
> $149.95
> Upper Induction Service (ServiceCategory)
> All with BG Cleaning Packages without EGR $189.00
> Throttle Plate Cleaning $78.90
> Induction Service, 3 Part $109.95
>
> Here's the problem, some of these Services have ServiceDetails.Details.
> Besides the fact that I'm still having problems with my query as it's part
> of a Many to Many junction table, there's is going to have to be several
> mores variables to track and only print when changed.
>
> My other option is to change the Query and rehit the database for each
> detail line(Maybe 20 seperate Recordset and database hits per page)
>
> Which is the best method?
>
>

[Aaron [SQL Server MVP]]

[url]http://www.aspfaq.com/2241[/url]

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Chris White" <nothanks.com> wrote in message
news:eU2dr74cEHA.1424@tk2msftngp13.phx.gbl...

> Here's my tables:
> [ServiceCategory]
> [ServiceCatID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [ServiceCategory] [char] (50) NULL ,
>
> [Services]
> [ServiceID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [ServiceCatID] [int] NULL ,
> [ServiceName] [char] (100) NULL ,
> [ServicePrice] [money] NULL ,
>
> [ServiceDetails]
> [ServiceDetailID] [int] IDENTITY (1, 1) NOT NULL (PRIMARY KEY)
> [Detail] [char] (50) NULL ,
>
> [ServiceDetailJoin]
> [ServiceID] [int] NOT NULL (PRIMARY KEY)
> [ServiceDetailID] [int] NOT NULL (PRIMARY KEY)
>
> I want to show all of this in a single table. I have ServiceCategory and
> ServiceName, ServicePrice coming across in a single query and displayed in

a

> single table. I have a variable keeping track of Service Name and only
> printing it(within it's own table row) when it changes.
> Here's a copy of the output, although it's kind of ugly.
>
> Oil Service (ServiceCategory)
> Oil Change Service $26.95
> Premier Oil Change Service with BG MOA Package (Price does not include
> synthetic oil) $32.95
> Transmission Service (ServiceCategory)
> Complete System Power Flush with BG Flush ATC Package
> $149.95
> Upper Induction Service (ServiceCategory)
> All with BG Cleaning Packages without EGR $189.00
> Throttle Plate Cleaning $78.90
> Induction Service, 3 Part $109.95
>
> Here's the problem, some of these Services have ServiceDetails.Details.
> Besides the fact that I'm still having problems with my query as it's part
> of a Many to Many junction table, there's is going to have to be several
> mores variables to track and only print when changed.
>
> My other option is to change the Query and rehit the database for each
> detail line(Maybe 20 seperate Recordset and database hits per page)
>
> Which is the best method?
>
>