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, ep, wafer_id, lot_id,
>        date_time, data_file_id,,
>        bottom, wf_file_path_id,,
>        vacc,
> 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_cst.target_name_id
> AND = data_cst.image_measurer_id
> AND = data_cst.meas_params_name_id
> AND = data_tool.category_id
> AND = 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 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_cst.target_name_id JOIN data_cstimage
> ON = data_cst.image_measurer_id JOIN data_measparams
> ON = data_cst.meas_params_name_id JOIN
>      (data_category JOIN data_tool
> ON = data_tool.category_id)
> ON = 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!
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