One-to-Many with a twist?

[pmg206]

I suspect the root cause is poor design (which I'm not opposed to altering if need be), or the end result is inherently a dumb idea.

 

The database tracks party events in an 'events' table, and which resources are assigned to that event (stored in the 'apparatus' table).  The lazy guy solution when that association was added was to give each apparatus a letter ID (primary key), and the 'events' table has a text field ('veh_group') of all lettered apparatus used for th event.

 

Works great to pull back which apparatus did what, but the query used there is a contains/like versus an =.

 

So the requirement now is to do reporting of frequency of apparatus, but using the COUNT command, it is successful only on those events with just one apparatus.  The SQL used here is:

 

SELECT count(`veh_group`) as total,truck_desc

FROM events join apparatus on events.veh_group = apparatus.truck_id

GROUP BY truck_desc

 

I figure the limitation on the JOIN is a distinct relationship only exists when a single apparatus works the event (so the a = a, g = g), however, when two or more are present, it breaks, since you can't join on a list of letters back to a single letter (i.e. "aefg" in 'events.veh_group' trying to join to the 'apparatus.truck_id'

 

Figuring the break here is the string trying to be joined back to a single ID, is there a relatively painless way to side-step this land-mine?  Thanks!  ~ Paul

 

 

[fenway]

The painless way is to normalize your database with a many-to-many table linking appartus to events.

[pmg206]

Thanks -- that got me moving by leaps in the right direction.  I created a xfer cross-reference table with only events.veh_group and apparatus.truck_id to join them up, and now have the results that were missing before -- but also am now missing the results I had before (thus in theory, two queries give me the complete picture).  Is there a convenient way to marry the two? 

 

The original query for single-resource events (just using a basic JOIN between events and apparatus tables):

 

SELECT count( `veh_group` ) AS total, truck_desc

FROM EVENTS JOIN apparatus ON events.veh_group = apparatus.truck_id

GROUP BY truck_desc

 

And the new query using the xfer and apparatus table:

 

SELECT count( xref.truck_id ) AS total, apparatus.truck_desc

FROM xref, apparatus

WHERE (apparatus.truck_id = xref.truck_id)

GROUP BY truck_desc

 

It feels like I could -- or in theory should -- be able to simply include it in the new query's WHERE clause, but that's not showing me the love.  Missing a step along the way, perhaps?  Thanks!

[fenway]

Sorry, which one is missing now?

[pmg206]

The missing step is how to (correctly or best) bridge between table 1 (events) and table 2 (apparatus) via the newly created table that should be bringing the two together.

 

If I run each query and add the results, it correctly totals the figures (had Excel check the results via a export file).

 

The question is if there is a means to merge the two queries into one, or if something from one query could simply be added to the other to accomplish the single result set with the total of both query's results.

[fenway]

Maybe I've totally lost you... what is it that you want to calculate?

[pmg206]

Basically, table 1 has a catalog of events (like parades), and table 2 is a list of vehicles.  In the events table, there is a varchar(10) field of lettes, a-h, or any combination thereof, indicating which vehicle(s) participated in the event.  (Such that a parade may be one vehicle, "a" for example, or may be several, like "abd, etc.)

 

The goal is to produce a list of total events participated, by each vehicle. (A simple join works fine for one truck per event, but the multiple vehicles at one event, given the varchar(10) in 'events' joined to char(1) of 'vehicles'.

 

In data examples, we may have:

 

eventID  veh_letters

        1          a

        2          abc

        3          bd

        4          d        (total of 4 events, 4 unique vehicles, with 7 vehicle-counts.

 

And the goal is to have a query that would kick out the result to look like

    vehicle      total

        a            2

        b            2

        c            1

        d            2

 

The simply JOIN results in the two events with a single vehicle (events 1 and 4, so results would look like [vehicle a = 1, d = 1], but 2 and 3 have two or more vehicles, so it skips those rows. The xfer table contains all valid combinations of events.veh_letter and the PK to the vehicle table, so 'xfer' table looks like, extending the example:

 

    veh_group    veh_id

          abc            a

          abc            b

          abc            c

          bd              b

          bd              d

 

The second query will bring back the count of the multiple vehicle events, so the results come back as [vehicle a = 1, b = 2, c = 1, d = 1].  Thus, if you manually add the two results sets together, we're back at the a = 2, b = 2, c = 1, d = 2. 

 

The question, then, is how -- or even if -- the result sets of the two could be merged into a single query result set similar to that done if one manually counted and grouped them on paper. 

 

[fenway]

Why is there a veh_group?

 

The xfer table should link event_id to veh_id, nothing more.