douran7929
2014-05-09 02:43
浏览 26

来自3个表的Oracle Query

I am having problem when querying data from 3 tables (FABRICATION, FABRICATION_QC, AND WEIGHT) and using their values in the PHP. My query is like this:

select fabrication.*,master_drawing.weight,
                (select fabrication_qc.marking_qc from fabrication_qc where fabrication_qc.head_mark=fabrication.head_mark) MARKING_QC,
                (select fabrication_qc.marking_qc_date from fabrication_qc where fabrication_qc.head_mark=fabrication.head_mark) MARKING_QC_DATE,
                (select fabrication_qc.marking_qc_sign from fabrication_qc where fabrication_qc.head_mark=fabrication.head_mark) MARKING_QC_SIGN
                from fabrication,fabrication_qc,master_drawing
                where fabrication.head_mark = master_drawing.head_mark";

and when I do this in the PHP to get that data into a table,

while (($row = oci_fetch_array($fabParse, OCI_BOTH)) != false) 
    { 
        echo '<tr>';
            echo '<td>'.$row['PROJECT_NAME'].'</td>';
            echo '<td>'.$row['HEAD_MARK'].'</td>';
            echo '<td>'.$row['ID'].'</td>';

            var_dump($row['MARKING_QC']);

PROJECT_NAME, HEAD_MARK, and ID work fine. Only the dumped MARKING_QC shows NULL in the output.

Can anyone tell me what I'm doing wrong here?

图片转代码服务由CSDN问答提供 功能建议

从3个表(FABRICATION,FABRICATION_QC,AND WEIGHT)查询数据时遇到问题并在PHP中使用它们的值。 我的查询是这样的:

  select fabrication。*,master_drawing.weight,
(从fabrication_qc中选择fabrication_qc.marking_qc,其中fabrication_qc.head_mark = fabrication.head_mark)MARKING_QC,\  n(从fabrication_qc中选择fabrication_qc.marking_qc_date,其中fabrication_qc.head_mark = fabrication.head_mark)MARKING_QC_DATE,
(从fabrication_qc中选择fabrication_qc.marking_qc_sign,其中fabrication_qc.head_mark = fabrication.head_mark)MARKING_QC_SIGN 
 from fabrication,fabrication_qc,master_drawing 
 where making fabrication  .head_mark = master_drawing.head_mark“; 
   
 
 

当我在PHP中执行此操作以将该数据放入表格时, < pre> while(($ row = oci_fetch_array($ fabParse,OCI_BOTH))!= false) { echo'&lt; tr&gt;'; echo'&lt; td&gt;'。$ row [' PROJECT_NAME']。'&lt; / td&gt;'; echo'&lt; td&gt;'。$ row ['HEAD_MARK']。'&lt; / td&gt;'; ech o'&lt; td&gt;'。$ row ['ID']。'&lt; / td&gt;'; var_dump($ row ['MARKING_QC']); \ n

PROJECT_NAME HEAD_MARK ID 正常工作。 只有转储的 MARKING_QC 在输出中显示 NULL

有谁能告诉我这里我做错了什么?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drqyxkzbs21968684 2014-05-09 02:49
    已采纳

    I am assuming this is the query you really want:

    select f.*, md.weight,
           (select fqc.marking_qc from fabrication_qc fqc where fqc.head_mark = f.head_mark) as MARKING_QC,
           (select fqc.marking_qc_date from fabrication_qc fqc where fqc.head_mark = f.head_mark) as MARKING_QC_DATE,
           (select fqc.marking_qc_sign from fabrication_qc fqc where fqc.head_mark = f.head_mark) as MARKING_QC_SIGN
    from fabrication f join
         master_drawing md
         on f.head_mark = md.head_mark;
    

    This removes the reference fabrication_qc in the outer from clause. That simply causes an unnecessary cartesian product. I also introduced table aliases to make the query more readable. And, put in proper explicit join syntax rather than the implicit joins.

    You can further simplify this to:

    select f.*, md.weight,
           fqc.marking_qc, fqc.marking_qc_date, fqc.marking_qc_sign
    from fabrication f join
         master_drawing md
         on f.head_mark = md.head_mark left outer join
         fabrication_qc fqc
         on fqc.head_mark = f.head_mark
    
    已采纳该答案
    打赏 评论
  • dongzu3511 2014-05-09 03:00

    I would write the query like this:

    select 
      f.project_name,
      f.head_mark,
      f.id,
      m.weight,
      qc.marking_qc, 
      qc.marking_qc_date,
      qc.marking_qc_sign 
    from fabrication as f 
    join master_drawing as m
      on  f.head_mark = m.head_mark
    left outer join fabrication_qc as qc      
      on  f.head_mark = qc.head_mark
    
    --  where (no criteria given)
    ;
    

    Try running the query from a query execution tool outside of your code, to confirm the results.

    打赏 评论

相关推荐 更多相似问题