来自3个表的Oracle Query

Question

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?

Answer 1

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