From: Larry Martell
Date: January 11 2013 10:17pm
Subject: query help
List-Archive: http://lists.mysql.com/mysql/228872
Message-Id: <CACwCsY6qP-UDWdnMtpT2Md=sX0TNV+RxR+LQUHL3oHm3ATs6Tg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8

I have this existing query that works fine:

SELECT data_tool.name as tool,
       MIN(data_cst.date_time) "start",
       MAX(data_cst.date_time) "end",
       data_cst.recipe_id,
       data_target.name as target,
       data_lot.name as lot,
       data_wafer.name as wafer,
       data_measparams.name as mp
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
WHERE data_target.id IN (130, 539)
AND data_cst.date_time BETWEEN '2010-01-11 00:00:00' AND '2013-01-11 23:59:59'
AND data_cst.tool_id IN (14,16)
GROUP BY wafer_id, data_cst.lot_id, data_file_id, target_name_id
HAVING count(*) < 100
ORDER BY start, tool


Now I need to add something to it. I have another table called
event_message_idx that has columns recipe_id, lot_id, tool_id,
date_time, and message_idx.

I need to find out how many rows in that table have message_idx = 'OM'
and how many have message_idx = 'SEM' joined with the above query on
recipe_id, lot_id, tool_id and has date_time between start and end.

I have not been able to figure out how to do this in one query (which
I'd really perfer to a sub query as these tables are very large and
the subquery performance has been poor in the past on this system).


I've been playing around with an left join like this:

SELECT data_tool.name as tool,
       MIN(data_cst.date_time) "start",
       MAX(data_cst.date_time) "end",
       data_cst.recipe_id,
       data_target.name as target,
       data_lot.name as lot,
       data_wafer.name as wafer,
       data_measparams.name as mp,
       event_message_idx.message_idx,
       COUNT(event_message_idx.message_idx)
FROM data_cst
LEFT JOIN event_message_idx
  ON event_message_idx.recipe_id = data_cst.recipe_id
  AND event_message_idx.message_idx IN ('OM', 'SEM')
  AND event_message_idx.lot_id = data_cst.lot_id
  AND event_message_idx.tool_id = data_cst.tool_id
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
WHERE data_target.id IN (130, 539)
AND data_cst.date_time BETWEEN '2010-01-11 00:00:00' AND '2013-01-11 23:59:59'
AND data_cst.tool_id IN (14,16)
GROUP BY wafer_id, data_cst.lot_id, data_file_id, target_name_id,
event_message_idx.message_idx
HAVING count(*) < 100
ORDER BY start, tool

But there are 2 issues here:

-I get a double the number of rows I want - one for OM and one for SEM
- I don't want that - I just want to know how many OM and SEM rows
there are (really I just want to know if there are 0 or more then 0 -
the actual count doesn't matter).

-I am not taking the date range into account - I only want to count
rows from event_message_idx that are between start and end and I can't
figure out how to do that.


Is this possible?

TIA!
-larry