List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:December 12 2012 5:13pm
Subject:Re: Help with left outer join
View as plain text  
On Tue, Dec 11, 2012 at 7:22 PM,  <hsv@stripped> 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!
Thread
Help with left outer joinLarry Martell11 Dec
  • Re: Help with left outer joinPeter Brawley11 Dec
    • Re: Help with left outer joinLarry Martell11 Dec
      • Re: Help with left outer joinPeter Brawley12 Dec
        • Re: Help with left outer joinLarry Martell12 Dec
  • Re: Help with left outer joinhsv12 Dec
    • Re: Help with left outer joinShawn Green12 Dec
      • Re: Help with left outer joinLarry Martell12 Dec
      • Re: Foreign-key naminghsv12 Dec
    • Re: Help with left outer joinLarry Martell12 Dec