Multiple Queries into one dataset

pbs commented on Jun 27, 2022

Is there a way to run two (or more) sql queries into the same dataset. The field headings would be the same.

For example, if I want to pull out all students with the firstname of David, and in a separate query wanted to pull out all staff with the firstname of David, could I do it with 2 different queries? - I know in this example I could build a UNION statement, but want to know if it is possible by running two separate queries.

I have tried this, but only get results for Staff.

        $sql = "SELECT 'STU' as PersonType,  Firstname, Lastname FROM Students WHERE Firstname = 'David'";
        
        $this->src('students')
        ->query($sql)
        ->saveTo($root);
        
        $sql = "SELECT 'STAFF' AS PersonType, Firstname, Lastname  FROM Staff WHERE Firstname = 'David'";        
        $root->query($sql)->saveTo($root);
        
       
        $root->pipe($this->dataStore('results'));

Sebastian Morales commented on Jun 28, 2022

Pls try to pipe to the same datastore multiple times instead:

        $sql_1 = ...;
        $this->src('students')
        ->query($sql_1)
        ->pipe($this->dataStore('results'));

        $sql_2 = ...;
        $this->src('students')
        ->query($sql_2)
        ->pipe($this->dataStore('results'));

When you use method saveTo($root), $root is like a reference variable (i.e a pointer to memory) which points to the current node. Thus, when you change it you would lose the last node that $root pointed to.

pbs commented on Jun 28, 2022

Thank you

Diego Soares commented on Dec 21, 2023

Thank you, Sebastian. I was trying to use a temporary table for my report with no success. Your post saved the day!

KoolReport's Forum

Multiple Queries into one dataset #2741

Build Your Excellent Data Report