On Tue, Dec 11, 2012 at 5:12 PM, Peter Brawley <peter.braw...@earthlink.net> wrote: > > On 2012-12-11 3:19 PM, Larry Martell wrote: > > 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. > > Explicit join syntax makes such queries easier to read, write, develop & > debug. Is this what you mean? > > > 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 > > RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id > > INNER JOIN data_target ON data_target.id = data_cst.target_name_id > > INNER JOIN data_measparams ON data_measparams.id = > data_cst.meas_params_name_id > > INNER JOIN data_category ON data_category.id = data_tool.category_id > > INNER JOIN data_tool ON data_tool.id = data_cst.tool_id > > 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' > > ORDER BY target_name_id, ep, wafer_id, lot_id, date_time;
Thanks very much for the reply. This is giving me: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. > 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? > > TIA! > -larry > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
