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 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.)

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