Hi,
I have a process that relies on three tables. There is a "Process"
table, a Process_description table and a third table that holds some
display decoration hints that depend on the perceived nature of each
process.
The decoration table is static. It looks like
decoration
{
severity_level int
label VARCHAR(30)
decoration VARCHAR(128)
}
the "Process" table looks like
Process
{
id int auto_increment
last_process_description int
...
}
Process_description
{
id int auto_increment
Process_id int
decoration_id int
...
}
What the above means to me is that a "Process" entry may have zero or
more "Process_description" entries linked to it through the
Process_description.Process_id field. Each Process_description will
have a "decoration" entry.
All this is peachy. I realized early that I would want to get
access to the LAST Process_description record entered so I added the
Process.last_process_description field to hold it. When I INSERT new
Process_description records I UPDATE the matching Process table entry
and set the new last_process_description field.
All of this is still peachy. I have this SELECT with two LEFT JOINS
that pulls the necessary data from all three tables and sets it all up
for me the way I want. That is I get every Process record in a
certain order that also contains the necessary data from the last
Process_description entry added (if there was one) and the display
hints for that record.
Time has passed and everyone has been happy. Until today. Now my
people would like to see everything they currently see plus the count of
Process_description records that go with each Process record.
I'm drawing a blank. Can this be done?
Here is a simplified query similar to the one I am doing:
SELECT P.id,
P.date,
D.browser_decoration,
PD.entry_date
FROM Process P
LEFT JOIN Process_description PD
ON P.last_logbook_entry_id = PD.id
LEFT JOIN Decoration D
ON D.level = PD.severity
What I'm hoping for is something like:
SELECT P.id,
P.date,
D.browser_decoration,
PD.entry_date
COUNT(PD.*)
FROM Process P
LEFT JOIN Process_description PD
ON P.last_logbook_entry_id = PD.id
LEFT JOIN Decoration D
ON D.level = PD.severity
How should I do this? Is there a better way to do any of this?
All the best,
Dean...K...
--
Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu
Imaging Technology Group / Beckman Institute
University of Illinois
405 North Mathews / Urbana, IL 61801 USA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
