I think you are on the right track. All you need to make your new
statement work is a GROUP BY clause. You can either reference your columns
by name or by position.
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
GROUP BY 1,2,3,4
That will both eliminate any duplicates and tell you how many of each
quadruple (id, date, browser_decoration, entry_date) you had. Problem
is.... entry_date derives from the same table that you want to collect the
counts on. You really should wrap that column with something like MAX()
and take it out of the GROUP BY clause so that the COUNT() predicate can
get a count of all of the records from Process_description and not just
how many have the same entry_date.
SELECT P.id,
P.date,
D.browser_decoration,
max(PD.entry_date) as 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
GROUP BY 1,2,3
If MAX(entry) date is not a good value for your results, I believe you
will be stuck using two queries. Your original query to return the data
your users expect and a second query just to compute the COUNT().
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Dean Karres <[EMAIL PROTECTED]> wrote on 10/04/2004 05:02:50 PM:
> 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]
>
