Displaying multiple values - Hard to explain here...

[MikeyJ]

Hi All!

Ok. I've got a query that's pulling data from multiple child tables based on
the primary key from the parent table and displaying it in a grid format. In
most cases, each child table will ony have one record related to the parent
table so that's easy to show in a cell. The problem comes when there is more
than one record in a child table. Basically, I need to show those values as a
comma delimited list in one cell but can't see how to do it properly.

Here's the query I'm currently using that works fine if the child tables one
have one related row in the parent table.

SELECT d.DID, d.FirstName, d.LastName, d.Setup,
p.PhoneNo,
bs.SBagNo,
bl.LBagNo,
bj.JBagNo,
bp.PBagNo,
soda.SodaBagNo,
uk.UKNo
FROM tblDrivers d
LEFT JOIN tblPhones p ON (d.DID = p.fkDID)
LEFT JOIN tblBagsSmall bs ON (d.DID = bs.fkDID)
LEFT JOIN tblBagsLarge bl ON (d.DID = bl.fkDID)
LEFT JOIN tblBagsJumbo bj ON (d.DID = bj.fkDID)
LEFT JOIN tblBagsPizza bp ON (d.DID = bp.fkDID)
LEFT JOIN tblSodaBags soda ON (d.DID = soda.fkDID)
LEFT JOIN tblUtensilKits uk ON (d.DID = uk.fkDID)
WHERE d.DID <> 0
AND D.Setup = 0

In this example, if tblBagsSmall has two bags (no's 3 & 7 for this example)
assigned to a particular driver which means two rows in the table, I need to
display them in my grid in the "Small Bags" column for that driver as "3, 7" so
a user can see a a quick glance what small bags that driver is currently
holding.

Any thoughts?

Thx!
Mike

[mxstu]

There may be a better way to do this, but maybe this might be a starting point
...

<!--- create test query --->
<cfset results = QueryNew("Driver,SBagNo")>
<cfset b = QueryAddRow(results, 3)>
<cfset b = QuerySetCell(results, "Driver", "Driver One", 1)>
<cfset b = QuerySetCell(results, "SBagNo", "3", 1)>
<cfset b = QuerySetCell(results, "Driver", "Driver One", 2)>
<cfset b = QuerySetCell(results, "SBagNo", "7", 2)>
<cfset b = QuerySetCell(results, "Driver", "Driver Two", 3)>
<cfset b = QuerySetCell(results, "SBagNo", "1", 3)>

<!--- output test --->
<table border="1">
<cfoutput query="results" group="Driver">
<tr><td>#Driver#</td>
<td><cfset bagList = "">
<cfoutput><cfset bagList = ListAppend(bagList, SBagNo)></cfoutput>
#bagList#
</td>
</tr>
</cfoutput>
</table>

[MikeyJ]

Thx mxstu!

I wasn't quite following your manual building of the query so I just embarked
on applying your output idea to my current query. It actually seems to work
quite nicely...until I add more than one record to a child table. When I do, I
actually get the proper values in the cell that represents the table that has
multiple values. The bad thing that's happening is that the other cells that
represent the other table values will duplicate their values equal to the
amount of records in that first table. I'll try and show you an example below.
1st is how it SHOULD look with 1 record in each child table except for the
small bag table which has two records. The second is how it DOES look.

Seems like I'm missing something simple....or not. ;-)

Mike

Driver Small Bag Large Bag Jumbo Bag
-------------------------------------------------------------------
Jim 1,3 4 12


Driver Small Bag Large Bag Jumbo Bag
-------------------------------------------------------------------
Jim 1,3 4,4 12,12

[Greg M]

I'm assuming you're using MS SQL Server. It's a little complex, but look
into the coalesce function [url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]
should be a good starting point. To keep your SQL statements simple you
might want to write this as a function.

Remember, you'll want to try and keep stuff like this in the data tier if
possible. Bringing the data up into CF and working on it there would be very
inefficient.


"MikeyJ" <mikeyj@pttracker.com> wrote in message
news:d9hp7s$n5q$1@forums.macromedia.com...

> Hi All!
>
> Ok. I've got a query that's pulling data from multiple child tables based
> on
> the primary key from the parent table and displaying it in a grid format.
> In
> most cases, each child table will ony have one record related to the
> parent
> table so that's easy to show in a cell. The problem comes when there is
> more
> than one record in a child table. Basically, I need to show those values
> as a
> comma delimited list in one cell but can't see how to do it properly.
>
> Here's the query I'm currently using that works fine if the child tables
> one
> have one related row in the parent table.
>
> SELECT d.DID, d.FirstName, d.LastName, d.Setup,
> p.PhoneNo,
> bs.SBagNo,
> bl.LBagNo,
> bj.JBagNo,
> bp.PBagNo,
> soda.SodaBagNo,
> uk.UKNo
> FROM tblDrivers d
> LEFT JOIN tblPhones p ON (d.DID = p.fkDID)
> LEFT JOIN tblBagsSmall bs ON (d.DID = bs.fkDID)
> LEFT JOIN tblBagsLarge bl ON (d.DID = bl.fkDID)
> LEFT JOIN tblBagsJumbo bj ON (d.DID = bj.fkDID)
> LEFT JOIN tblBagsPizza bp ON (d.DID = bp.fkDID)
> LEFT JOIN tblSodaBags soda ON (d.DID = soda.fkDID)
> LEFT JOIN tblUtensilKits uk ON (d.DID = uk.fkDID)
> WHERE d.DID <> 0
> AND D.Setup = 0
>
> In this example, if tblBagsSmall has two bags (no's 3 & 7 for this
> example)
> assigned to a particular driver which means two rows in the table, I need
> to
> display them in my grid in the "Small Bags" column for that driver as "3,
> 7" so
> a user can see a a quick glance what small bags that driver is currently
> holding.
>
> Any thoughts?
>
> Thx!
> Mike
>

[mxstu]

Greg M - Very Slick. I'd forgotten about the COALESCE method. I would be curious to know exactly how much more efficient one method is versus the other...

[Greg M]

Keeping the data on the SQL server avoids the data transfer needed to build
the query in CF, that alone is a big performance hit. Unless you run SQL and
CF on the same server, then it's not as bad, but still not the best
solution.

I like to look at it this way:

The SQL server involves years of optimization and refinment for one thing,
database manipulation. Keep your data on the SQL server as long as possible,
it knows how to handle it.

The ColdFusion server is really refined for data input and output, let it do
what it does best.

"mxstu" <webforumsuser@macromedia.com> wrote in message
news:d9ib4p$g9v$1@forums.macromedia.com...

> Greg M - Very Slick. I'd forgotten about the COALESCE method. I would be
> curious to know exactly how much more efficient one method is versus the
> other...
>
>

[mxstu]

>> Keeping the data on the SQL server avoids the data transfer needed to build

>>the query in CF, that alone is a big performance hit.

I agree that reducing the amount of data transferred improves performance.

>>The SQL server involves years of optimization and refinment for one thing,
>>database manipulation. Keep your data on the SQL server as long as possible,
>>it knows how to handle it.

Yes, it is definitely optimized for set-based operations, and in most cases
it's probably the best choice, but IMO I don't know that it is *always* the
best tool for the job once you get into some of the more complex data
manipulations.

I haven't used sql server's UDFs that much, so I'm curious what kind of
performance they provide with large tables or if you were to use a UDF on 10
separate columns in a query. What has your experience been with them?


what it does best.

[Greg M]

I havn't done any real world large database work in a few years, but from my
experience, SQL almost always scaled better, as long as the DB was designed
properly. Of course, if you use SQL like a big flat file DB, it tends to
degrade pretty quickly.

I try and use the SQL server itself for as much of the DB work as possible.
I won't generally use the SQL server to format data for display though, CF
shines in that area.

Overall, I've found that a well defined interface to your SQL data via
stored procedures is really the best way to go. (And lets people port the
application quicker to other front-ends such as ASP).

As far as UDF's, like any other function, it really depends on what you are
doing. They do have most of the advantages of stored procedures, which is a
plus.

"mxstu" <webforumsuser@macromedia.com> wrote in message
news:d9ih1b$ndj$1@forums.macromedia.com...

>>> Keeping the data on the SQL server avoids the data transfer needed to
>>> build
> >>the query in CF, that alone is a big performance hit.

>
> I agree that reducing the amount of data transferred improves performance.
>

> >>The SQL server involves years of optimization and refinment for one
> >>thing,
> >>database manipulation. Keep your data on the SQL server as long as
> >>possible,
> >>it knows how to handle it.

>
> Yes, it is definitely optimized for set-based operations, and in most
> cases
> it's probably the best choice, but IMO I don't know that it is *always*
> the
> best tool for the job once you get into some of the more complex data
> manipulations.
>
> I haven't used sql server's UDFs that much, so I'm curious what kind of
> performance they provide with large tables or if you were to use a UDF on
> 10
> separate columns in a query. What has your experience been with them?
>
>
> what it does best.
>
>

[mxstu]

>> I havn't done any real world large database work in a few years, but from my

>>experience, SQL almost always scaled better, as long as the DB was designed
>>properly. Of course, if you use SQL like a big flat file DB, it tends to
>>degrade pretty quickly.

True. A poor design becomes evident rather quickly and like I said, for most
situations is is the best tool for the job of manipulating data. However, I had
one application where the level and type of complex manipulation, concurrency
and speed requirements, simply could not be accomplished in the database alone.
That project was not the norm, but even with a good design, IMO databases
aren't designed or lets just say optimized for certain types of operations.

In looking at the COALESCE() method again, maybe I'm missing something, but
I'm beginning to suspect you cannot "get there from here".

The COALESCE() method works fine for populating a single variable with a comma
delimited list, but the OP needs to return a query containing multiple rows.
As you cannot combine data retrieval and SET @variable operations, in the same
statement, I'm not sure that this method is an option here. Any ideas, or am I
missing something obvious here?

[Greg M]

Look into something like this, I think it's more as what you want. Use it as
a function in the sql statement.

[url]http://www.dotnetspider.com/technology/kbpages/1113.aspx[/url]

CREATE FUNCTION dbo.GetCommaSeperatedValues(@ID int)
RETURNS varchar(100) AS
BEGIN
declare @RtnValue varchar(100)
Select @RtnValue = COALESCE(@RtnValue+',' , ' ') + Author from Authors where
id=@ID
return @RtnValue
END


The Select statement would like the following..

Select id,dbo.GetCommaSeperatedValues(id) from Authors group by id


"mxstu" <webforumsuser@macromedia.com> wrote in message
news:d9it30$9dj$1@forums.macromedia.com...

>>> I havn't done any real world large database work in a few years, but
>>> from my
> >>experience, SQL almost always scaled better, as long as the DB was
> >>designed
> >>properly. Of course, if you use SQL like a big flat file DB, it tends to
> >>degrade pretty quickly.

>
> True. A poor design becomes evident rather quickly and like I said, for
> most
> situations is is the best tool for the job of manipulating data. However,
> I had
> one application where the level and type of complex manipulation,
> concurrency
> and speed requirements, simply could not be accomplished in the database
> alone.
> That project was not the norm, but even with a good design, IMO databases
> aren't designed or lets just say optimized for certain types of
> operations.
>
> In looking at the COALESCE() method again, maybe I'm missing something,
> but
> I'm beginning to suspect you cannot "get there from here".
>
> The COALESCE() method works fine for populating a single variable with a
> comma
> delimited list, but the OP needs to return a query containing multiple
> rows.
> As you cannot combine data retrieval and SET @variable operations, in the
> same
> statement, I'm not sure that this method is an option here. Any ideas, or
> am I
> missing something obvious here?
>
>
>

[mxstu]

>>Look into something like this, I think it's more as what you want. ....

>> CREATE FUNCTION dbo.GetCommaSeperatedValues(@ID int)

Thanks. I did figure that out later, but I was hoping there was a way to
create a more generic UDF, that didn't require a subquery, operating on each
row in the main table, as the OP has multiple tables to query. Although, it
does seem like a lot of tables...

[MikeyJ]

Hi Guys,

Thanks for the replies. I definitely learned something from these posts
concerning the COALESCE function, I just don't think I'm ready to head down
that path yet. ;-)

Can either of you see why I might be getting those duplicate displays I
describe in the 3rd post?

Thanks!
Mike

[mxstu]

Thanks for the replies. I definitely learned something from these posts
concerning the COALESCE function, I just don't think I'm ready to head down
that path yet. ;-)

Without a fuller understanding of your database structure, it's hard to say
anything with certainty. However, the COALESCE method may not be the right
path to take here. Your query joins a number of different tables. Using a UDF
that performs a subquery for each row of the main table, and for each table in
your join, seems to be very expensive IMO, which would only increase as your
tables grow. The CFOUTPUT / list method is not ideal either, but I'm sticking
with it until something better comes along ;-)

On that subject.. I don't know that the query you posted actually reflects the
real table/column names in your database, but the types of information in the
different tables (tblSodaBags, tblUtensilKits, etc) does seem very similar. Was
there a specific reason that the data is separated into 6 or 7 different tables
as opposed to using one table?

Can either of you see why I might be getting those duplicate displays I
describe in the 3rd post?

Based on the way the query is structured, the results may repeat the same
column value, on more than one row. Since the ListAppend() function does not
create a list of "distinct" values, so you need to check the list and only
append a value if it is not already in the list. Something like this should
work ..



<cfquery name="getData" datasource="yourDSN">
SELECT d.DID, d.FirstName, d.LastName, d.Setup,
p.PhoneNo, bs.SBagNo, bl.LBagNo, bj.JBagNo,
bp.PBagNo, soda.SodaBagNo, uk.UKNo
FROM tblDrivers d
LEFT JOIN tblPhones p ON (d.DID = p.fkDID)
LEFT JOIN tblBagsSmall bs ON (d.DID = bs.fkDID)
LEFT JOIN tblBagsLarge bl ON (d.DID = bl.fkDID)
LEFT JOIN tblBagsJumbo bj ON (d.DID = bj.fkDID)
LEFT JOIN tblBagsPizza bp ON (d.DID = bp.fkDID)
LEFT JOIN tblSodaBags soda ON (d.DID = soda.fkDID)
LEFT JOIN tblUtensilKits uk ON (d.DID = uk.fkDID)
WHERE d.DID <> 0
AND d.Setup = 0
ORDER BY d.DID
</cfquery>



<table border="1">
<!--- note: cfoutput "group" attribute matches the ORDER BY clause in CFQUERY
--->
<cfoutput query="getData" group="DID">
<tr><td>#DID#</td>
<!--- note: must clear variable "itemList" before each use --->
<td><cfset itemList = "">
<cfoutput><cfset itemList = ListAppendUnique(itemList, PhoneNo)></cfoutput>
#itemList#
</td>
</tr>
</cfoutput>
</table>

<!--- note: put code in a udf for clarity --->
<cfscript>
function ListAppendUnique(list, value) {
if (ListLen(list) eq 0 OR ListContainsNoCase(list, value) eq 0) {
return ListAppend(list, value);
}
else {
return list;
}
}
</cfscript>

[MikeyJ]

Wow! That was a perfect solution, thx! I thought it might be along those
lines but couldn't see how to get there from here. ;-)

Was there a specific reason that the data is separated into 6 or 7 different
tables as opposed to using one table?

Well, the concept is that a user chooses to edit a row associated with a
particular driver (the row shows the info you helped me display properly). The
interface then shows multi-select listboxes populated by each of the tables in
question allowing the user to check this equipment in or out for that driver.

For example, if the Small Bags table contains 10 rows representing bags 1-10,
and row five has a DriverID in the fkDID field it'll show that as checked out
to the driver. If it's a "0" in the row it shows it as available to BE checked
out if needed. Also, it won't show bags that aren't available (are checked out
by other drivers already).

Does this sorta kinda explain it? Do you envision a more efficient way of
doing it?

Thx again!
Mike

[mxstu]

So it sounds like equipment is assigned to different drivers, but I'm still not
understanding the reason why the different types of equipment are in separate
tables. Are you storing them in separate tables because you want to keep the
different types of equipment separate for the interface lists... or is there
some other reason?

[MikeyJ]

I think mostly for the separation reason you mentioned.

I'm not sure how else it could be done? Since each of those 7 equipment
tables will hold upwards of 50 items, how might those reside in one table? Am
I overnormalizing the table by relating each of those tables to the driver
table by DriverID and keeping them separate?

Mike

[mxstu]

A table with 350+ records (7x50) is not large by database standards. Some
databases can handle millions of records, though I wouldn't recommend that in
Access ;-) What database are you using, by the way?

You're the most familiar with your data and the needs of your application.
However, if all the separate tables contain similar items (i.e. equipment) with
similar properties, then I would probably put them in a single table named
"Equipment". I would then create another table to store the types of equipment
and add FK to the "Equipment" table to indicate the type or category of each
piece of equipment. This way, you could simply filter on the equipment "type"
to retrieve the data for each of your select lists.



[Equipment]
EquipmentID Title EquipmentTypeID
=============== ============ ================
1 Item A 1 <-- Phone
2 Item B 2 <-- Small Bags
3 Item C 3 <-- Large Bags
4 Item D 4 <-- Jumbo Bags
....etc..


[EquipmentType]
EquipmentTypeID Title
=============== ============
1 Phone
2 Small Bags
3 Large Bags
4 Jumbo Bags
....etc..

[MikeyJ]

Interesting. I'd like to fool with that a bit. Would you still store the DriverID in the Equipment table or create another table that associates drivers with equipment?

Thx!
Mike

[mxstu]

My preference is usually a separate table, since "DriverID" is not really a property of "Equipment", but that is just my opinion.

[MikeyJ]

Ok, you led me down this path...now you're in for it. ;-)

So, now I've got 4 tables:

tblDrivers
--------------
DID, LastName, FirstName

tblEquipment
-------------------
EQID, EQName, fkETID

tblEquipmentType
-------------------------
ETID, ETName

tblDriversEquip (a "bridge" table containing DriverID and EquipID's)
-------------------------------
DEID, fkDID, fkEQID

I want to populate a table showing each driver and which equipment they have,
just like I was doing previously. What does the query look like for this now?
Do I still SELECT from tblDrivers and then JOIN on the others? The fields I'm
needing to show are:

Driver Name (LastName, FirstName)
Phone (EName where ETID = 1)
Utensil Kit (EName where ETID = 2)
Small Bag (EName where ETID = 3)
Large Bag (EName where ETID = 4)
Jumbo Bag (EName where ETID = 5)
Pizza Bag (EName where ETID = 6)
Soda Bag (EName where ETID = 7)

Sometimes there will be more than one record for an Equipment type but you
already helped me display those with your UDF (although maybe that will change
now with this implementation!?)

Thx!
Mike