...

View Full Version : joining multiple tables



Mike1963
05-16-2011, 03:45 PM
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
05-16-2011, 04:04 PM
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
05-16-2011, 04:12 PM
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
05-16-2011, 07:22 PM
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
05-16-2011, 07:33 PM
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
05-19-2011, 02:07 PM
so from what i`m gathering is theres no way to join several different tables unless theres a common field?

Fumigator
05-19-2011, 03:33 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum