Double Select inside 1 function

[eluser]pipiet06[/eluser]
Hi,,
i am confused with my code.
i wanna get the result of my query with call 2 different queries.

is it possible to get data like this:

Code:

function get_leftColHeader()
        {
            $sql1 = "SELECT DISTINCT
                        eqplan, A.serialnumber
                    FROM
                        tblUnitNumber A
                    LEFT JOIN tblDashboard B ON A.serialnumber = B.serialnumber
                    WHERE A.idsite = 'bhj' and A.idfleet = '793C' and A.idprefix = '4AR'";
            
            $sql2 = "SELECT
                        a.serialnumber, a.idcomponent,
                        a.idmodifier, idstatus_ref, c.idfleet,
                        a.dates, MAX(a.trendnumber) as trendnumber, c.eqplan,
                        a.bcfnumber, a.rating, h.idgroup
                    FROM tbldashboard a
                    RIGHT JOIN
                        (SELECT serialnumber, idcomponent, idmodifier, max(dates) as dates
                    FROM tbldashboard
                    GROUP BY serialnumber, idcomponent, idmodifier) AS b
                    ON a.serialnumber = b.serialnumber
                    AND a.idcomponent = b.idcomponent
                    AND a.idmodifier= b.idmodifier
                    AND a.dates = b.dates
                    LEFT JOIN tblUnitNumber c ON a.serialnumber = c.serialnumber
                    LEFT JOIN tblDashboardComponent d ON a.idcomponent = d.idcomponent
                    LEFT JOIN tblunitnumber f ON a.serialnumber = f.serialnumber
                    LEFT JOIN tbl_ed_rating g ON a.idcomponent = g.idcomponent
                    AND a.idmodifier = g.idmodifier
                    INNER JOIN tbl_ed_idevent h ON g.id_event = h.id_event
                    AND g.idcat = h.idcat
                    WHERE f.idsite = 'bhj'
                    AND f.idfleet = '793C'
                    AND h.idgroup = 'DME'
                    GROUP BY  serialnumber, idcomponent, idmodifier";
                
            $query = $this->db->query($sql2, $sql1);
            // $data['result'] = $query->result_array();
            return $query->result_array();
        }

this is in the controller

Code:

function get_leftColHeaders()
        {    
            $query1 = $this->dmedashboard->get_leftColHeader();
            // $query2 = $this->dmedashboard->get_rating();
            foreach ($query1 as $row){
                $json1[] = array(
                    // 'eqplan' => $row['eqplan'],
                    // 'serialnumber' => $row['serialnumber'],
                    'idcomponent' => $row['idcomponent'],
                    'idmodifier' => $row['idmodifier'],
                    'rating' => $row['rating']
                );
            }
            
            $rows['rows'] = array_merge_recursive($json1);
            
            echo json_encode($rows);
        }

can someone help me to solve my code?

[eluser]bobbob[/eluser]
Are you looking for UNION?

Code:

$sql1 = "SELECT DISTINCT
                        eqplan, A.serialnumber
                    FROM
                        tblUnitNumber A
                    LEFT JOIN tblDashboard B ON A.serialnumber = B.serialnumber
                    WHERE A.idsite = 'bhj' and A.idfleet = '793C' and A.idprefix = '4AR'
                    UNION
                    SELECT
                        a.serialnumber, a.idcomponent,
                        a.idmodifier, idstatus_ref, c.idfleet,
                        a.dates, MAX(a.trendnumber) as trendnumber, c.eqplan,
                        a.bcfnumber, a.rating, h.idgroup
                    FROM tbldashboard a
                    RIGHT JOIN
                        (SELECT serialnumber, idcomponent, idmodifier, max(dates) as dates
                    FROM tbldashboard
                    GROUP BY serialnumber, idcomponent, idmodifier) AS b
                    ON a.serialnumber = b.serialnumber
                    AND a.idcomponent = b.idcomponent
                    AND a.idmodifier= b.idmodifier
                    AND a.dates = b.dates
                    LEFT JOIN tblUnitNumber c ON a.serialnumber = c.serialnumber
                    LEFT JOIN tblDashboardComponent d ON a.idcomponent = d.idcomponent
                    LEFT JOIN tblunitnumber f ON a.serialnumber = f.serialnumber
                    LEFT JOIN tbl_ed_rating g ON a.idcomponent = g.idcomponent
                    AND a.idmodifier = g.idmodifier
                    INNER JOIN tbl_ed_idevent h ON g.id_event = h.id_event
                    AND g.idcat = h.idcat
                    WHERE f.idsite = 'bhj'
                    AND f.idfleet = '793C'
                    AND h.idgroup = 'DME'
                    GROUP BY  serialnumber, idcomponent, idmodifier";

$query = $this->db->query($sql1);

Actually this won't work as the column count is different

[eluser]pipiet06[/eluser]
i have tried that.
and the result is like this
|eqplan|serialnumber| | | | | | | |
|HT001 | 04ARhjh | | | | | | | |--> from sql1
| | | hfjhg | kshgk | kjldsgk | jhdfk | lsdhljgl | jsdfhlk | sjkdfh |--> from sql2

that is not what i want.

i want the result like:
|eqplan|serialnumber|idcomponent|idmodifier|idfleet| dates |trendnumber|rating |idgroup |
|HT001 | 04ARhjh | hfjhg | kshgk |kjldsgk| jhdfk | lsdhljgl | A | OE |
|_________| |________________________________________________________|
| |
v v
from sql1 from sql2