List:General Discussion« Previous MessageNext Message »
From:hsv Date:December 12 2012 12:22am
Subject:Re: Help with left outer join
View as plain text  
>>>> 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,,
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.)

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