Kevin,
Use a UNION ALL query and force your ordering. I will assume your simplistic
data as
below (i.e. Food: text, Packages: Text, Options: Text) (assuming SQL Server for
simplicity)
SELECT
r.reservation_id, r.client_name
, 'Food' Item_Type, rf.food_text Item_Text
FROM
dbo.Reservations r
INNER JOIN
dbo.Reservation_Food rf.reservation_id = r.reservation_id
UNION ALL
SELECT
r.reservation_id, r.client_name
, 'Packages', rp.package_text
FROM
dbo.Reservations r
INNER JOIN
dbo.Reservation_Packages rp.reservation_id = r.reservation_id
UNION ALL
SELECT
r.reservation_id, r.client_name
, 'Options', ro.option_text
FROM
dbo.Reservations r
INNER JOIN
dbo.Reservation_Option ro.reservation_id = r.reservation_id
ORDER BY
reservation_id, Item_Type, Item_Text
UNION ALL does not apply a DISTINCT over the entire query when done, therefore
it is more
efficient [when you know what your data looks like].
Of course, I'd do this in a stored procedure. If you want to list specific
reservation_ids, you could have WHERE clauses for each UNIONed query, or wrap
it:
SELECT
a.field_names_here
FROM (
entire query from above here
) as a
WHERE
a.reservation_id = @reservation_id
This should compile to the same as placing the WHERE clauses in each query
part, but be
easier to maintain.
--
David L. Penton
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
---------- Original Message -----------
From: "Kevin Fricke" <[EMAIL PROTECTED]>
To: "CFLIST" <[email protected]>
Sent: Mon, 5 Dec 2005 12:31:22 -0600
Subject: [DFW CFUG] Grouping etc.
> Ok, here is a more detailed example of what I am trying to do.......forgot
> to mention this part :)
>
> I have a table - reservations. For each reservation, there are a series of
> items that can be attached to this reservation, food, packages, options.
> There is a join table that joins all of these with the reservation.
>
> Therefore a reservation may look like this:
>
> Reservation ID: 1
> Client Name: Kevin
>
> Food
> ----------
> Nachos
> Tacos
> Ice Cream
>
> Packages
> ----------
> Live Music
> Casino
>
> Options
> ----------
> Margarita Machine
> Bartender
>
> Now, I know that with group I can run the query and get all of the
> reservations, group by the reservation id and then
> <cfoutput>#food#</cfoutput> to lists the food options. However, I have been
> unsuccessful in grouping the additional lists. It will not work properly.
> That is why I was hoping that there was a way to group a list into a query
> result field. I also figured that doing everything in the actual db query
> would be faster....
>
> Can anyone help? Or maybe this doesn't make any sense?????
>
> Kevin
>
> _______________________________________________
> List mailing list
> Reply to DFWCFUG:
> [email protected]
> Subscribe/Unsubscribe:
> http://lists1.safesecureweb.com/mailman/listinfo/list
> List Archive:
> http://lists1.safesecureweb.com/mailman/private/list
> DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
------- End of Original Message -------
_______________________________________________
List mailing list
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archive:
http://lists1.safesecureweb.com/mailman/private/list
DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
