>>>> 2012/12/11 16:19 -0500, Larry Martell >>>>
I have this query:
SELECT data_target.name, ep, wafer_id, lot_id,
date_time, data_file_id, data_cstimage.name,
bottom, wf_file_path_id, data_measparams.name,
vacc, data_category.name
FROM data_cst, data_target, data_cstimage, data_measparams,
data_category, data_tool
WHERE data_cst.target_name_id IN (38018, 29947, 28330)
AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00'
AND data_target.id = data_cst.target_name_id
AND data_cstimage.id = data_cst.image_measurer_id
AND data_measparams.id = data_cst.meas_params_name_id
AND data_category.id = data_tool.category_id
AND data_tool.id = data_cst.tool_id
ORDER BY target_name_id, ep, wafer_id, lot_id, date_time
My problem is that when data_cst.image_measurer_id is NULL I don't get
that data_cst row even though all the other part of the where clause
are TRUE. I understand why that is, but in that case I want the row,
but with NULL in the data_cstimage.name column. I think I need a left
outer join, but I've been messing with this for hours, and I can't get
the syntax right. I've googled it, but all the examples are simple
with just 2 tables. Can someone help me with this?
<<<<<<<<
Modern forms do not give a left join if one uses WHERE-clause to reduce a full
cross-join to an inner join. It is better to start with something like this,
FROM data_cst JOIN data_target
ON data_target.id = data_cst.target_name_id JOIN data_cstimage
ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams
ON data_measparams.id = data_cst.meas_params_name_id JOIN
(data_category JOIN data_tool
ON data_category.id = data_tool.category_id)
ON data_tool.id = data_cst.tool_id
but I am not too sure where to bracket "data_tool". When you have put it into a
'FROM'-clause with 'JOIN', not comma, separating the tables, with the same
outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'.
Maybe you want it between "data_cstimage" and "data_measparams".
(Are all the distinct "id"s really needed? When one joins on a field with the
same name in both tables, one may use 'USING', and only the common field, with
neither NULL, shows up in the output.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
