On Tue, Dec 11, 2012 at 7:22 PM, <h...@tbbs.net> wrote: >>>>> 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.)
I'm not familiar with the USING clause. I'll have to look into that. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
