From: Larry Martell Date: December 12 2012 5:13pm Subject: Re: Help with left outer join List-Archive: http://lists.mysql.com/mysql/228798 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Tue, Dec 11, 2012 at 7:22 PM, 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:0= 0:00' > AND data_target.id =3D data_cst.target_name_id > AND data_cstimage.id =3D data_cst.image_measurer_id > AND data_measparams.id =3D data_cst.meas_params_name_id > AND data_category.id =3D data_tool.category_id > AND data_tool.id =3D 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 lik= e this, > > FROM data_cst JOIN data_target > ON data_target.id =3D data_cst.target_name_id JOIN data_cstimage > ON data_cstimage.id =3D data_cst.image_measurer_id JOIN data_measparams > ON data_measparams.id =3D data_cst.meas_params_name_id JOIN > (data_category JOIN data_tool > ON data_category.id =3D data_tool.category_id) > ON data_tool.id =3D 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 th= e same outcome as now, then you have to decide before which 'JOIN' to put t= he '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 fie= ld, with neither NULL, shows up in the output.) I'm not familiar with the USING clause. I'll have to look into that. Thanks= !