List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:December 11 2012 11:11pm
Subject:Re: Help with left outer join
View as plain text  
On Tue, Dec 11, 2012 at 5:12 PM, Peter Brawley
<peter.brawley@stripped> wrote:
>
> On 2012-12-11 3:19 PM, Larry Martell wrote:
>
> 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.
>
> Explicit join syntax makes such queries easier to read, write, develop &
> debug. Is this what you mean?
>
>
> 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
>
> RIGHT JOIN data_cstimage   ON data_cstimage.id = data_cst.image_measurer_id
>
> INNER JOIN data_target     ON data_target.id = data_cst.target_name_id
>
> INNER JOIN data_measparams ON data_measparams.id =
> data_cst.meas_params_name_id
>
> INNER JOIN data_category   ON data_category.id = data_tool.category_id
>
> INNER JOIN data_tool       ON data_tool.id = data_cst.tool_id
>
> 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'
>
> ORDER BY target_name_id, ep, wafer_id, lot_id, date_time;

Thanks very much for the reply. This is giving me:

ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause'

But category_id is a column in data_tool.


> 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?
>
> TIA!
> -larry
>
>
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