Adding new table to existing query

Question

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 a 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 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? I'm thinking it's not and I've have to use 2 queries (which will really complicate the app), or a subquery (which I'm also struggling to write).

Answer 1

calculate those in a subquery, eg

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,
        COALESCE(a.totalOM, 0) totalOM,
        COALESCE(a.totalSEM, 0) totalSEM
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
        LEFT JOIN 
        (
            SELECT  recipe_id, lot_id, tool_id,
                    SUM(CASE WHEN message_idx = 'OM' THEN 1 ELSE 0 END) totalOM,
                    SUM(CASE WHEN message_idx = 'SEM' THEN 1 ELSE 0 END) totalSEM
            FROM    event_message_idx
            WHERE   date_time BETWEEN '2010-01-11 00:00:00' AND '2013-01-11 23:59:59'
            GROUP   BY recipe_id, lot_id, tool_id
        ) a ON  data_cst.recipe_id = a.recipe_id AND
                data_cst.lot_id = a.lot_id AND
                data_cst.tool_id = a.tool_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