joining multiple tables

[Mike1963]

how would i go about joining more then 1 table that have nothing in common? I am wanting to get a total count from several different tables then sort the results by month and amount per month

[Fumigator]

The whole point of a join is to bring together data with something in common. Without that common something, a join is not the right tool for the job.

You probably want to use a UNION instead.

[Mike1963]

I tried UNION and UNION all and it does put out the data from all 3 tables but does not bring them together as a total count or separate the result by amount per month

[Fumigator]

You can get amount by month with "GROUP BY month". You may have to run a separate query to get a count of multiple tables:

SELECT (SELECT COUNT(*) FROM T_TABLE1)
+ (SELECT COUNT(*) FROM T_TABLE2)
+ (SELECT COUNT(*) FROM T_TABLE3)

[Mike1963]

heres what i am trying to accomplish:

http://classify.kellywebserv.com/3tables.php

<?php

include ('.global.php');


?>                 

                 <?php
                 
$result7 = mysql_query("SELECT ad_id FROM ads
 UNION ALL 
 SELECT ad_id FROM ads_announce 
 UNION ALL 
 SELECT ad_id FROM ads_jobs") or die("Sql error : " . mysql_error());

$num_rows4 = mysql_num_rows($result7);

$result2 = mysql_query("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_announce
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_jobs group by month(ad_date)") or die("Sql error : " . mysql_error());
                $data1 = array();
                 $approved = array();
                 $i = 0;
while ($row = mysql_fetch_array($result2)) {
$data1[$i] = (int) $row['num1'];
$approved[$i] = (int) $row['approved'];
          $cats1[$i] = $row['admonth'];
          $i++;
                         
}

$result2a = mysql_query("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads where approved='1'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads_announce where approved='1'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved1 from ads_jobs where approved='1' group by month(ad_date)") or die("Sql error : " . mysql_error());

                 $approved1 = array();
                 $i = 0;
while ($row = mysql_fetch_array($result2a)) {
$approved1[$i] = (int) $row['approved1'];
          $cats1[$i] = $row['admonth'];
          $i++;
                         
}

                 $query3 = ("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads where approved='0'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved  from ads_announce where approved='0'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads_jobs where approved='0' group by month(ad_date)") or die("Sql error : " . mysql_error());


                                  $result3 = mysql_query($query3);
                 $unapproved = array();
                 $i = 0;
while ($row = mysql_fetch_array($result3)) {
$unapproved[$i] = (int) $row['unapproved'];
          $i++;
                         
}


$data1 = json_encode($data1);
$cats1 = json_encode($cats1);
$approved = json_encode($approved);
$approved1 = json_encode($approved1);
$unapproved = json_encode($unapproved);

                 ?>

                                  <?php
echo "Total number of ads=" .$data1. "Should equal the TOTAL Ads in the system which is " .$num_rows4. "<br><br>";
echo "Months=" .$cats1. " Should combine the months which in this case should be April and May<br><br>";
echo "Total number of ads approved=" .$approved1. " Should combine the total number of ads in all 3 tables that are approved and seperated by month.<br><br>";
echo "Total number of ads unapproved=" .$unapproved. " Should combine the total number of ads in all 3 tables that are not approved and seperated by month.<br><br>";


/* from this point down has been hand counted and not generated from the code*/

echo 'The actual numbers are as follows:<br>
<table border="1">
<tr>
<th>Table</th><th>Total Ads</th><th>Month(s)</th><th>Approved</th><th>Not Approved</th></tr>
<tr>
<td>ads</td><td>29</td><td>April, May</td><td>4</td><td>25</td></tr>

<tr><td>ads_announce</td><td>11</td><td>April</td><td>4</td><td>7</td></tr>

<tr><td>ads_jobs</td><td>21</td><td>May</td><td>1</td><td>20</td></tr>
</table>';

echo 'Final output should read:<br>
Total Number of ads=61<br>
Months = April, May<br>
Total number of ads approved=[7,2] (April=7, May=2)<br>
Total number of ads unapproved=[20,32] (April=20, May=32)';
?>

[Mike1963]

so from what i`m gathering is theres no way to join several different tables unless theres a common field?

[Fumigator]

Oh there's a way, it's called a Cartesian join. But there is typically no point in doing so and it usually doesn't give the results you're after. It's easy to do; just join two tables with no qualifier on the join.