Jump to content
saynotojava

Joining two select queries with WHERE clause

Recommended Posts

Posted (edited)

I have two mysql queries , where currently  i run second query by loop, so would like to reduce loading time by making single query. However, it seems there is some issue with WHERE clause which i can put it into only one query when using LEFT/RIGHT JOIN with Union and i use WHERE in both queries.

Here is queries:

SELECT `Table1`.Va, `Table1`.Va2, count(*) AS count,SUM(Co) AS sum FROM  `Table1` WHERE  `Table1`.`Dt` between '2019-08-27 00:00:00'
 AND '2019-08-27 23:59:59'  GROUP BY `Table1`.Va, `Table1`.Va2 

SELECT `Table2`.Va, `Table2`.Va2, SUM(Payout) AS count , count(*) AS mu FROM  `Table2` WHERE `Table2`.`DateTime` between '2019-08-27 00:00:00' AND '2019-08-27 23:59:59'  GROUP BY `Table2`.Va, `Table2`.Va2

 

Edited by saynotojava

Share this post


Link to post
Share on other sites

Are those the two queries you mentioned, or two of your attempts at combining the queries.

Your first references Visits.Va2  yet there is no mention of a Visits table.

You also mention LEFT/Right JOIN and UNION but they seem to be absent too, as is any kind of JOIN.

Why is the second query being run in a loop?

In other words, can you provide a better description of what you are trying to, preferably with the table structures.

Share this post


Link to post
Share on other sites
1 minute ago, Barand said:

Are those the two queries you mentioned, or two of your attempts at combining the queries.

Your first references Visits.Va2  yet there is no mention of a Visits table.

You also mention LEFT/Right JOIN and UNION but they seem to be absent too, as is any kind of JOIN.

Why is the second query being run in a loop?

In other words, can you provide a better description of what you are trying to, preferably with the table structures.

Visits thing was error, updated post to properly show first query(it's table1 instead). Also, Using Left/Right Union was one was with which i tried to solve it, does not need to be same method but it seems it does what i trying to accomplish. At the moment i loop query with table2 , and when there is no result, i output it as zero.

Share this post


Link to post
Share on other sites

I still have no idea what you are trying to do.

Instead of showing your attempts, why don't you show

  • the original two queries, the one that doesn't appear in a loop and the second one which does.
  • some sample data
  • expected results

Share this post


Link to post
Share on other sites

Ok here is full currently working code with 2 queries, and goal is to have only one query instead.

$result = $conn->query("SELECT `Table1`.Value1, `Table1`.Value2, count(*) AS count,SUM(Co) AS sum FROM  `Table1` WHERE  `Table1`.`Dt` between '2019-08-27 00:00:00'
 AND '2019-08-27 23:59:59' GROUP BY `Table1`.Value1, `Table1`.Value2  ; ");


echo'
	<table class="zui-table zui-table-highlight-column">
    <thead>
        <tr>
            <th>Value1</th>
            <th>Value2</th>
                  </tr>
    </thead>
    <tbody>';
  while($row = $result->fetch_assoc()) {

  $str = $row['Value1'];   
  $str = strtoupper($str);
  
echo"
	   <tr>
            <td>echo $str</td>
            <td>echo {$row['Value2']}\"></td>
            <td>{$row['count']}</td>
            <td>
";
$resultb = $conn->query("SELECT `Table2`.Value3, `Table2`.Value4, SUM(Payout) AS count , count(*) AS mu FROM  `Table2` WHERE `Table2`.`Dt` between '2019-08-27 00:00:00' AND '2019-08-27 23:59:59' GROUP BY `Table2`.Value3, `Table2`.Value4  ; ");
if ($resultb->num_rows > 0) {
	 while($rowb = $resultb->fetch_assoc()) {


$amo=substr($rowb['count'], 0, 5);
echo $amo;
$numb = $rowb['mu'];
 	}
}
else {

}
echo "
            </td>
             <td>";


            echo" </td>
                  <td></td>
                      <td>
";


echo"
                      </td>
                      <td>";

                      echo"</td>
                      <td>
";








echo"
                      </td>
          </tr>
"; 
} echo'
  </tbody>
</table>

 

Share this post


Link to post
Share on other sites

Those two queries are totally unrelated, so, for each record in the first set of results you produce an identical set of results from the second query.

Either confirm that is what you want to do or tell us what is you really want to do - or i'm giving up.

Share this post


Link to post
Share on other sites
21 minutes ago, Barand said:

Those two queries are totally unrelated, so, for each record in the first set of results you produce an identical set of results from the second query.

Either confirm that is what you want to do or tell us what is you really want to do - or i'm giving up.

They share common values, like Value1 is same as Value3  and Value2 is same as Value4. They also share datetime column which is same.

And here is some data output example::

Table1                                 Table2

Value1 A Value2 fror  Value3 A Value4 fror

Value1 B Value2 frbo

Current code counts number of each duplicates, in this case first query number of values A/fror in Table1 , and same in Table2. In case table2 does not contain anything .then i show it as zero.

So end result look like this:

A Fror 500 2 -In first table there 500  entries with value A Fror, while in second two

B frbo 100 0-In first table there is 100 entires with value B frbo, while second does not contain anything

Share this post


Link to post
Share on other sites

Sorry, but I can't see how you get different results from table2 for each of the table1 records. I must be missing something.

Stick around and maybe one of the others here can see what's going on and offer help.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.